Why doesn't copyTo(… PASTE_VALUES) work in the

One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place. Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty. But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended. Why is this? Is there a better way to work around this problem? Here are my three macros.

function Step1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getActiveRange();
  CopyRangeToNewSheet(spreadsheet, range);
  spreadsheet.getCurrentCell().offset(-1, 6).activate();
  FillInHeaders(spreadsheet);
  spreadsheet.getCurrentCell().offset(1, -4).activate();
  FillInFormulas(spreadsheet);
  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

function Step2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var keepers = spreadsheet.getRange('G:J');
  keepers.activate();
  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var discard = spreadsheet.getRange('A:F')
  discard.activate();
  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());
};

function BothSteps() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getActiveRange();
  CopyRangeToNewSheet(spreadsheet, range);
  spreadsheet.getCurrentCell().offset(-1, 6).activate();
  FillInHeaders(spreadsheet);
  spreadsheet.getCurrentCell().offset(1, -4).activate();
  FillInFormulas(spreadsheet);
  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  var keepers = spreadsheet.getRange('G:J');
  keepers.activate();
  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var discard = spreadsheet.getRange('A:F')
  discard.activate();
  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());
};

function FillInHeaders(spreadsheet) {
  spreadsheet.getCurrentCell().setValue('First Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Last Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Middle Name');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setValue('Email');
}

function FillInFormulas(spreadsheet) {
  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');
  spreadsheet.getCurrentCell().offset(0, 2).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');
}

function CopyRangeToNewSheet(spreadsheet, range) {
  var newSheet = spreadsheet.insertSheet(1);
  spreadsheet.setActiveSheet(newSheet, true);  
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}

Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps: https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing

回答1:

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...
  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  // Force formulas to calculate and pending writes to be written.
  SpreadsheetApp.flush();
  // Read formula results and save as values.
  var keepers = spreadsheet.getRange('G:J');
...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...
  SpreadsheetApp.flush();
  var toKeep = spreadsheet.getRange('G:J');
  toKeep.setValues(toKeep.getValues());
  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);
}

Note that you still want the call to .flush().

回答2:

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.