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

Question

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 ImpNewClient = SpreadsheetApp.getActive().getSheetByName("ImpNewClient");
  ImpNewClient.activate();
  var URL = ImpNewClient.getRange('B106').getValue();
  var SS = SpreadsheetApp.openByUrl(URL);  
  var Quotes = SS.getSheetByName("Quotes");
  var lastRow = Quotes.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var source = Quotes.getRange("f2:f"+lastRow);
  var Values = source.getValues();
  var Formulas = source.getFormulas();
  var quotes = ss.getSheetByName('Quotes');
  var dest = quotes.getRange("f2:f"+lastRow);
for (var i = 0; i < lastRow-1; i++) {
  if (Formulas[i][0] !== "") {
    dest.setValue(Values[i][0]);
  } else {
    dest.setFormula(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.

Answer 1

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);
  }
}

Answer 2

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.setFormula(Formulas[i][0]);
  } else {
    dest.setValue(Values[i][0]);
  }
}

Update

Based on your spreadsheets the a final code should look like this:

function test() {

  var dest_ss    = SpreadsheetApp.getActiveSpreadsheet();
  var newClient_sheet = dest_sheet.getSheetByName('ImpNewClient');
  var url        = newClient_sheet.getRange('B106').getValue();

  var source_ss  = SpreadsheetApp.openByUrl(url);
  var src_sheet  = source_ss.getSheetByName('Quotes');
  var lastRow    = src_sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var source     = src_sheet.getRange('f2:f' + lastRow);

  var values     = source.getValues();
  var formulas   = source.getFormulas();

  var dest_sheet = dest_ss.getSheetByName('Quotes');

  for (var i = 0; i < lastRow - 1; i++) {
    var cell = dest_sheet.getRange('f'+ (i + 2));
    if (formulas[i][0] !== '') { 
      cell.setFormula(formulas[i][0])
    }
    else { 
      cell.setValue(values[i][0])
    }
  }

  dest_sheet.activate()
}

And it's really confusing when you're using variables like ss / SS, and quotes / Quotes simultaneously. I'd advice you to use names like dest_ss / src_ss, dest_sheet / src_sheet.

And first letter in upper case (Values, Formulas, etc) means a class name usually, it makes sense don't use it for variables (values, formulas, etc).