Simple Google Sheets Macro to copy-paste updating formula output into different cells not working

Question

I am trying to do a very basic copy paste macro where it:

• Changes the value in cell H20
• Through a formula, the value in cell I20 updates
• Copy Pastes the value in cell I20 to J20

It then completes the cycle again:

• Changes the value in cell H20 (a different value to the first time around)
• Through a formula, the value in cell I20 updates
• Copy Pastes the value in cell I20 to J21

And once again into J22.

What I am finding is that once the macro has run, the value in cell J20, J21 and J22 is the same, when they should be different, since the value in I20 changes during the process. It's almost like it's pasting the value in I20 at once to all three cells at the end, rather than throughout.

Does anyone know why this is and how I can fix it? I made this macro by recording it, not coding.

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('2');
  spreadsheet.getRange('J20').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('3');
  spreadsheet.getRange('J21').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('4');
  spreadsheet.getRange('J22').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Answer 1

You need to refresh your sheet between each changes of value

function test(){
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('2');
  spreadsheet.getRange('J20').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush()
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('3');
  spreadsheet.getRange('J21').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush()
  spreadsheet.getRange('H20').activate();
  spreadsheet.getCurrentCell().setValue('4');
  spreadsheet.getRange('J22').activate();
  spreadsheet.getRange('I20').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

flush() Applies all pending Spreadsheet changes.

explanation

How about an ELI5 analogy:

as far as i know, when you run the script, google takes an image of the sheet and runs based on it. Because you have a formula based on the value you entered, and because you take the result, you have to tell google: refresh the sheet and take a new image.