0

I'm just getting my way around Google Sheets script, and I'm finding it a little difficult to zero in on the problem. The code apparently copies empty values from column 4 to column 3, when I use the Range.setFormulaR1C1 in the line just above the Range.copyTo statement. 

function cleanUpNames() {

var currentSheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var currentSheetLastRow = currentSheet.getLastRow();

//Range of cells in the third column, that are just names
var firstNames = currentSheet.getRange('C2:C' + currentSheetLastRow);

//Range of cells in the fourth column
var newFirstNames = currentSheet.getRange('D2:D' + currentSheetLastRow);

//Using the fourth column to replace period character(.) with empty string
var formulaString = '=SUBSTITUTE(R[0]C[-1],".","")';
newFirstNames.setFormulaR1C1(formulaString);

//This line is giving empty values on the third column, instead of pasting the actual formula results
newFirstNames.copyTo(firstNames, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

};

However, when I do away with the code that sets formulas and enter the corresponding formula from the UI, then run the following code, it works just fine. I'm really not sure where I'm getting this wrong. Any help would be very much appreciated.

function cleanUpNames() {

var currentSheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var currentSheetLastRow = currentSheet.getLastRow();

//Range of cells in the third column, that are just names
var firstNames = currentSheet.getRange('C2:C' + currentSheetLastRow);

//Range of cells in the fourth column
var newFirstNames = currentSheet.getRange('D2:D' + currentSheetLastRow);

//This line is giving empty values on the third column, instead of pasting the actual formula results
newFirstNames.copyTo(firstNames, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

};
Nik L
  • 39
  • 1
  • 7

1 Answers1

1

Before the copyTo code line add the following

SpreadsheetApp.flush();

Related

NOTE: AFAIK setFormulaR1C1(formula), where formula is a string, should be used to set the formula of a single cell. To set the formula of multiples cells, instead use setFormulasR1C1(formulas), where formula is an Array of strings. For details checkout the example on the official documentation: https://developers.google.com/apps-script/reference/spreadsheet/range#setformulasr1c1formulas

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Hi Ruben, thanks for your answer! The `setFormulaR1C1(formula)` works just fine. The problem is with the immediate `Range.copyTo` statement that is unable to copy the formula results. – Nik L Feb 10 '19 at 22:09
  • 1
    Awesome! Adding that `SpreadsheetApp.flush();` made it work as expected. You saved me a lot of trouble, thank you so much! – Nik L Feb 10 '19 at 22:32