TypeError: ss.getSheetByName is not a function

Question

My code is meant to take data from a user input form and insert a new row to a spreadsheet:

function addNewRow(rowData) {
  
    const currentDate = new Date();


    const ss = SpreadsheetApp.getActiveSheet;
    const ws = ss.getSheetByName('DataTable');
        ws.appendRow(rowData.userName,rowData.userProject, rowData.workDate, rowData.timeSpent, currentDate);
}

Yet when I run the code it returns

TypeError: ss.getSheetByName is not a function

If you log the ss variable, what is the output? – Gilles Heinesch Apr 05 '21 at 11:34 — Gilles Heinesch, Apr 05 '21 at 11:34
It completes without issue. – poppaGottz Apr 06 '21 at 13:02 — poppaGottz, Apr 06 '21 at 13:02

score 3 · Answer 1 · answered Apr 05 '21 at 11:38

3

Use const ss = SpreadsheetApp.getActive() to get the spreadsheet. Note the parentheses. The function will not get called if you omit them.

answered Apr 05 '21 at 11:38

doubleunary

13,842
3
18
51

score 1 · Answer 2 · edited Sep 16 '22 at 21:48

The error "getSheetByName is not a function" occurs when running a function in Google Apps Script when this method is called upon a variable that wasn't assigned a Class Spreadsheet object.

In this case, the OP forgot to include the parenthesis at the end of SpreadsheetApp.getActiveSheet causing Spreadsheet.getActiveSheet method to be assigned to the variable instead of executing this method to return a Class Sheet object. This also happens when chaining another method that doesn't return a Class Spreadsheet Object, i.e. Spreadsheet.getActiveSheet().getName() returns a string.

In the OP case the solution is straightforward as already was show in the previous answer. Other cases having the same error might require a different solution, to find it,

Check the correct spelling and syntax. For this it's very helpful to understand the pretty basics of JavaScript, i.e, JavaScript is case sensitive. This means that getActiveSheet is not the same as getactivesheet.
Check the official guides in https://developers.google.com/apps-script/overview, more specifically https://developers.google.com/apps-script/guides/sheets.
Check the official reference in https://developers.google.com/apps-script/reference. In this specific case you might search for getSheetByName directly in this link. In this case this method is used in several examples that might be helpful to understand it.
Going deep in the Google Apps Script editor, use the autocomplete feature to have instant feedback about it the method is available for the object assigned to the variable: Type a dot after the variable name, this will show a list of the available methods, continue typing the method name to filter the list suggestions, if there isn't any suggestion, then there is very likely that there is a problem on the variable declaration. Ref. https://developers.google.com/apps-script/guides/services/#using_autocomplete
Use the Google Apps Script debugger. Ref. https://developers.google.com/apps-script/guides/support/troubleshooting#debugging
Go deep in learning JavaScript. There a lot of resources to start learning JavaScript. By default Google Apps Script use V8 to execute scripts. Please bear in mind that Web APIs like the DOM API aren't available in V8. It's worthy to note that there are some JavaScript functions that aren't supported in Google Apps Script like async as well some Global Objects like Promise

TypeError: ss.getSheetByName is not a function

2 Answers2

Related

Linked

Related