google-apps-script google-sheets google-sheets-macros

Inconsistent results in Google Sheets macros?

I have the following spreadsheet (and this is it's ideal beginning state):

When you enter a number in the yellow field (B55), C55 and D55 are calculated (based on other stuff not relevant to the problem).

Here's what I need a macro to do: (1) Copy B58, and paste the value ("1") in B55. This will change the values of C55 and D55.(2) Copy C55, paste the value in C58. (3) Copy D55, paste the value in D58. (4) Repeat steps 1-4, but this time with row 59. (5) Repeat, but with row 60. (6) Etc.

So if the script only ran once (on B58), it should look like ...

If the script runs with 4 rows of year data (B58-B61), and this is what the script code below should do, the output SHOULD look like this:

Really it should run for 20 iterations, with a final value of "100" years. But for brevity and to illustrate the problem, I stopped at "15". But let's say, for example, I enter 100 in the yellow field, I get ...

If I were run the script at this point, however, I get ...

See how they are all the same value? The macro I recorded is copying the last entry in C55 and D55, instead of the calculated values for 1, then 5, then 10, etc.

What am I doing wrong?

Here's the script it created:


function TEST1() {
  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B58').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C58').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D58').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B59').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C59').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D59').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B60').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C60').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D60').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B61').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C61').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D61').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Note: This is a simplification of a larger requirement. A macro is necessary, I can't turn the table into multiple formulas.

Solution

You want to copy B58 to B55. At this time, C55 and D55 are calculated. You want to copy the calculated values of C55:D55 to C58:D58.
- You want to run this flow for each row.
You want to achieve this by modifying your script.

I could understand like above. If my understanding is correct, how about this answer. Please think of this as just one of several answers.

Modification point:

In your case, in order to calculate the values, please use SpreadsheetApp.flush(). After SpreadsheetApp.flush() was run, the calculated values are copied.

Pattern 1:

In this pattern, your script is modified. Please modify as follows.

Modified script:

function TEST1() {
  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B58').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush(); // Added
  spreadsheet.getRange('C58').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D58').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B59').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush(); // Added
  spreadsheet.getRange('C59').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D59').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B60').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush(); // Added
  spreadsheet.getRange('C60').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D60').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B55').activate();
  spreadsheet.getRange('B61').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush(); // Added
  spreadsheet.getRange('C61').activate();
  spreadsheet.getRange('C55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D61').activate();
  spreadsheet.getRange('D55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Pattern 2:

In this pattern, the start row and end row you want to retrieve are declared, and the values are copied in the for loop. Please modify as follows.

Modified script:

function TEST1() {
  var startRow = 58; // Please set the start row. In this case, it's row 58.
  var endRow = 61; // Please set the start row. In this case, it's row 61.

  var spreadsheet = SpreadsheetApp.getActive();
  for (var row = startRow; row <= endRow; row++) {
    spreadsheet.getRange("B" + row).copyTo(spreadsheet.getRange("B55"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    SpreadsheetApp.flush();
    spreadsheet.getRange("C55:D55").copyTo(spreadsheet.getRange("C" + row + ":D" + row), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  }
}

Reference:

flush()