When I use getValues/setValues between sheets I loose formulas; when I us getFormulas/setFormulas I-CodePudding

I'm new to coding but think I need to loop through so getFormulas is returned for non formula data and setFormulas is returned for formulas. Something like the following code but this is just a guess; it's the loop statement in particular I need help with.

function test() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var NewClient = SpreadsheetApp.getActive().getSheetByName("NewClient");
  NewClient.activate();
  var URL = NewClient.getRange('B106').getValue();
  var SS = SpreadsheetApp.openByUrl(URL);  
  SpreadsheetApp.setActiveSpreadsheet(SS);
  var spreadsheet = SpreadsheetApp.getActive();
  var Quotes = SpreadsheetApp.getActive().getSheetByName("Quotes");
  Quotes.activate();
  var lastRow = Quotes.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var Quotes = SS.getSheetByName("Quotes");
  var source = Quotes.getRange("f2:f" lastRow);
  var Values = source.getValues();
  var Formulas = source.getFormulas();
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var spreadsheet = SpreadsheetApp.getActive();
  var quotes = ss.getSheetByName('Quotes');
  quotes.activate();
  var dest = quotes.getRange("f2:f" lastRow);
  for (var i=0;i< lastRow;i  ) {
    if(dest.setFormulas[Formulas[i]] = ""){
      dest.setValues(Values[i][0]);
      dest.setFormulas(Formulas[i][0]);
      }
  
  }}

I tried getValues/setValues which returned everything as values including the formulas; conversely with getFormulas/setFormulas only the formulas were returned with non formula values returning an empty cell. I assume this is has something to do with moving data between different workbooks.

What I am expecting is equivalent to what you would get if you used right click copy paste special formulas on a sheet range - which would return values as values and formulas as formulas.

CodePudding user response：

Try to change the loop:

for (var i = 0; i < lastRow; i  ) {
  if(dest.setFormulas[Formulas[i]] = ""){
    dest.setValues(Values[i][0]);
    dest.setFormulas(Formulas[i][0]);
  }
}

This way:

for (var i = 0; i < lastRow; i  ) {
  if (Formulas[i][0] == "") {
    dest.setValues(Values[i][0]);
  } else {
    dest.setFormulas(Formulas[i][0]);
  }
}

CodePudding user response：

Description

Although I'm not able to test it, and I hope I didn't make any typos, I believe this sample script will copy both values and formulas to the destination range. getFormulas() returns an array of strings representing the formulas in each cell. These strings can simply be copied to the values array and then the values array can be put back using setValues().

It is customary to start variable names with a lowercase letter. Upper case is usually used for Class or Global variables.

All of the sheet activation, spreadsheet activation serve no purpose that I can see. You simply need the source spreadsheet ss2 and the destination spreadsheet ss1.

Once you let newClient = SpreadsheetApp.getActive().getSheetByName("NewClient"); the variable newClient retains a reference to that instance of the sheet named "NewClient". You don't need to activate it to use it or any of its properties or methods.

Script

function myTest() {
  try {
    let ss1 = SpreadsheetApp.getActiveSpreadsheet();
    let newClient = ss1.getSheetByName("NewClient");
    let url = newClient.getRange('B106').getValue();
    let ss2 = SpreadsheetApp.openByUrl(url);
    // Get quotes from ss2
    let quotes = ss2.getSheetByName("Quotes");
    let lastRow = quotes.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    let source = quotes.getRange("f2:f" lastRow);
    let values = source.getValues();
    let formulas = source.getFormulas();
    // Get destination range in ss1
    quotes = ss1.getSheetByName('Quotes');
    let dest = quotes.getRange("f2:f" lastRow);
    for( let i=0; i<lastRow-1; i   ) {  // Started in row 2
      if( formulas[i][0] !== "" ) {
        values[i][0] = formulas[i][0];
      }
    }
    dest.setValues(values);
  }
  catch(err) {
    console.log(err);
  }
}