CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
SporeLamm21
SporeLamm21

Reputation: 3

Exception: First select an active worksheet

I just started working with Google Apps Script so don't be to harsch^^. My function: "GetData" works flaweless if triggered manually, but if my time based trigger activates it I get an error via email.

the error:

Exception: First select an active worksheet.

function pullData() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var lr = sheet.getLastRow()+1;
 var date = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy")
 
 var ss = SpreadsheetApp.openByUrl('XXXX');
 var range = ss.getRange('C32').getValue();

 sheet.getRange('A' + lr).setValue(date);
 sheet.getRange('B' + lr).setValue(range);
 sheet.getRange('A32').setValue('done');
}

Upvotes: 0

Views: 610

Answers (2)

Wicket
Wicket

Reputation: 38160

One of the problems with your code is that getLastRow() is a method of Class Sheet but SpreadsheetApp.getActiveSpreadsheet() returns a Spreadsheet object not Sheet object

Similar to what was suggested in the previous answer, replace

var sheet = SpreadsheetApp.getActiveSpreadsheet();

by

var sheet = SpreadsheetApp.getActiveSheet();

Please bear in mind that the first sheet will be returned. If you need to return a different sheet, instad of getActiveSheet() you could use getSheetByName(name) or you could SpreadsheetApp.getActiveSpreadsheet().getSheets() to get a collection of Sheet objects then you could a bracket notation SpreadsheetApp.getActiveSpreadsheet().getSheets()[2] to get a Sheet object based on its position.

Another situation that might be a problem is that the getRange(a1Notation) from the Spreadsheet Class when using a range reference withoug specifing the sheet name will get the corresponging cell of the first sheet. If you are looking to work with the first sheet, this will not be a problem.

Upvotes: 0

Marios
Marios

Reputation: 27350

Issues

When you use a time-trigger for a particular function that interacts with your spreadsheet file, it is not recommended to use active sheets.

Issue 1:

Replace:

var sheet = SpreadsheetApp.getActiveSpreadsheet();

with

var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");

Assuming you have multiple sheets in your spreadsheet file.

If you use:

SpreadsheetApp.getActiveSpreadsheet().getLastRow() you get the last row with content of the active sheet. How can you be sure that the active sheet is the sheet that you are interested in getting its last row number?

Issue 2:

Here:

 var ss = SpreadsheetApp.openByUrl('XXXX');
 var range = ss.getRange('C32').getValue();

again, you don't define the particular sheet you want to work with.

Replace that with:

 var ss = SpreadsheetApp.openByUrl('XXXX').getSheetByName("Sheet2");
 var range = ss.getRange('C32').getValue();

Solution

Modify Sheet1 and/or Sheet2 with the names of the sheets in your particular scenario/problem.

function pullData() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
 var lr = sheet.getLastRow()+1;
 var date = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy")
 
 var ss = SpreadsheetApp.openByUrl('XXXX').getSheetByName("Sheet2");
 var range = ss.getRange('C32').getValue();

 sheet.getRange('A' + lr).setValue(date);
 sheet.getRange('B' + lr).setValue(range);
 sheet.getRange('A32').setValue('done');
}

References:

getSheetByName(name)

Upvotes: 3

Related Questions