getRange/setValues does not always work

Question

I help maintain a Google spreadsheet where new data is added via a HTML form.

When it comes to add new data the insertion point of the new data depends on one of the form fields (Application Received date).

The script finds where in the sheet the data should be inserted and does 3 things:

Inserts a blank row at the correct location
Copies the row above (so formulas and conditional formatting are copied)
Replaces the data in the cells from the copy with the values entered into the form

The issue is cells A to I are value based (populated from the form) and so are cells M to O, but cells J,K,L are calculations based on some cells in A to I.

This means I have to make 2 calls to getRange/setValues and sometimes the second call (the call to set cells M,N,O does not work. The result is a new row created with the correct data in cells A to I (and thus J,K,L) but cells M,N,O stay as whatever is in those cells in the row above.

Here is the relevant code.

// Assign object data for cells A to I
var newvalues = [
            [ username, applyDate, maritalStatus, sponsorApprovalDate, processingOffice, inProcessDate, extraDocsRequestedDate, nonVisaExempt, decisionMadeDate ]
          ];

// Set cells A to I with data from form
sheet.getRange('A' + startingRowIndex + ':I' + startingRowIndex).setValues(newvalues);

// Now assign object data for cells M to O
newvalues = [
            [ coprReceivedDate, location, notes ]
          ];

// Set cells M to O with data from form
sheet.getRange('M' + startingRowIndex + ':O' + startingRowIndex).setValues(newvalues);

As stated above the second sheet.getRange('...').SetValues() call fails to set the values.

Any ideas?

how often it happens? if it's rare, this is normal behavior in case the script fails to execute fully (since there are no transactions in the sheets api). — Zig Mandel
make a minimal sample that repros this. we dont know what else is in your code or your data. — Zig Mandel
also try adding spreadsheetApp.flush() in between those calls and let us know if it helps. — Zig Mandel

Mogsdad Mogsdad · Accepted Answer · 2015-10-30T04:01:57

Instead of completely recalculating the locations of your output ranges, you could get an "anchor" point at the start of the row, then use the Range.offset() method to define additional ranges relative to the anchor.

// Assign object data for cells A to I
var newvalues = [
            [ username, applyDate, maritalStatus, sponsorApprovalDate, processingOffice, inProcessDate, extraDocsRequestedDate, nonVisaExempt, decisionMadeDate ]
          ];

// Get range "anchor" for data from form
var newRow = sheet.getRange('A' + startingRowIndex );

// Set cells A to I with data from form
newRow.offset(0,0,newvalues.length,newvalues[0].length).setValues(newvalues);

// Now assign object data for cells M to O
newvalues = [
            [ coprReceivedDate, location, notes ]
          ];

// Set cells M to O with data from form
newRow.offset(0,13,newvalues.length,newvalues[0].length).setValues(newvalues);

getRange/setValues does not always work

1 Answers