Google Sheets – copyTo Function Failing to Copy Data

google sheetsgoogle-apps-script

I have a pretty simple problem that is for some reason killing me. I have the following script which should just autofill a formula down column O, then copy the values to column P (excuse my amateur coding):

function processData() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var lastWorkDate = dateCheck();
  sheet.getRange('O1').setValue("=index('"+lastWorkDate+"'!O:O,match(B1,'"+lastWorkDate+"'!B:B,0))");
  sheet.getRange('O1').autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  Utilities.sleep(1000);
  freeze();
};

function freeze() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  sheet.getRange('P1').activate();
  sheet.getRange('O:O').copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

However, the copying just isn't doing anything. The script will successfully autofill O:O, but will fail to copy the values to P:P.

Manually running freeze() itself after running processData() (and thus already having O:O filled) works fine, so I figured it might be running too quickly and added the Utilities.sleep(1000), however, that's not helping either.

Best Answer

In your processData() function, use the SpreadsheetApp.flush(); function just before calling freeze();. This will force any pending changes (such as updates to a large number of cells) to finish right away. See the API documentation for more information: SpreadsheetApp.flush()

Related Solutions

Google-sheets – Make dynamic array blank based on adjacent values in Google Spreadsheets

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Related Topic