CODEWITHSUNDEEP
arraysdebugginggoogle-apps-scriptgoogle-sheetsgoogle-apps-script-editor
chris
chris

Reputation: 1

Difficult in debugging app script

how to resolve google app scripting error: "TypeError: Cannot call method "getRange" of null. (line 16, file "Code")" when debugging the following script:

function getEmails_(q) {
    var emails = [];
    var threads = GmailApp.search(q);
    for (var i in threads) {
        var msgs = threads[i].getMessages();
        for (var j in msgs) {
            emails.push([msgs[j].getBody().replace(/<.*?>/g, '\n')
                .replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')
            ]);
        }
    }
    return emails;
}

function appendData_(sheet, array2d) {
    sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function saveEmails() {
    var array2d = getEmails_("Still looking for Python Programming help");
    if (array2d) {
        appendData_(SpreadsheetApp.getActiveSheet(), array2d);
    }
}

Upvotes: 0

Views: 352

Answers (2)

James Sheard
James Sheard

Reputation: 139

Have you set the sheet variable?

var sheet = SpreadsheetApp.getActiveSheet();

Do Logger.log(array2d); to check the data is being set in the array.

It'll be because it cant find the sheet.

I sometimes had this error and had to rename the sheet and it worked. It's strange.

Upvotes: 1

J&#233;r&#233;my G.
J&#233;r&#233;my G.

Reputation: 1

You are trying to access getRange() on a null sheet because in your context SpreadsheetApp.getActiveSheet() return null (ie. there is no active spreadsheet)

More context could help to find a way to provide an active Spreadsheet to your function.

You can by instance open a spreadsheet with :

  • SpreadsheetApp.open(file)
  • SpreadsheetApp.openById(id)
  • SpreadsheetApp.openByUrl(url)

And then set it active SpreadsheetApp.setActiveSpreadsheet(newActiveSpreadsheet) https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

There is other ways to make a spreadsheet active, for example when a trigger is fired (ex. notifyOnFormSubmit).

To ensure you have an active spreadsheet, you can test SpreadsheetApp.getActiveSheet() before using it :

var spreadsheet = SpreadsheetApp.getActiveSheet();
if (spreadsheet) {
  Logger.log('spreadsheet url:' + spreadsheet.getUrl());
}

Upvotes: 0

Related Questions