CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
M S
M S

Reputation: 1

Google script, issue with using setValues due to an undefined array

I have a script to check for new data from one spreadsheet to another and I've created an array that is giving me an error when compiling. The error is TypeError: Cannot read property "length" from undefined. (line 29, file "Code") Line 29 is the last line, ".setValues(arr);" I've looked at several examples of using setValues and can't see my error.

Thanks in advance.

var arr = [];
var rangeval = range.getValues()
    .forEach(function (r, i, v) {
        if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
    });
var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
    .getSheetByName('Sheet1');
destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, arr.length, arr[0].length)
    .setValues(arr);

Upvotes: 0

Views: 468

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7387

The error indicates that arr[0] is undefined at execution time.

Since getValues() returns a two dimensional array, it looks to me like if (r[1] == 'Amber') will always evaluate to False.

You probably want (r[i][1] == 'Amber')

Generally when using setvalues this way I do a check first:

if(arr.length > 0)
  destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, arr.length, arr[0].length).setValues(arr);

To troubleshoot further, you can use the built in debugger to step through execution, or use Logger.log() calls and check the logs under via Logs under the View menu.

Upvotes: 0

Related Questions