All of a sudden getting an error in Google Sheets from this function that existed for years. Did they change something recently?

I wrote a function a while ago to determine if a sheet in a workbook already exists. Here is the function:

function SheetNameExists(name)
 return (ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0);

All of a sudden within the last month or so I get a TypeError: Cannot call method "toString" of null. (line 105, file "Code"). Line 105 is the return statement.

Up until this error started happening I have had no issues with any of the scripts on this Sheet. I did not change anything in the code-behind.

Did Google change up something without providing deprecation support?

EDIT: I forgot ActiveSpreadsheet() was not a built-in function when I wrote all this out. This is the code I wrote to get it:

function ActiveSpreadSheet()
 return SpreadsheetApp.getActiveSpreadsheet();

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

I think that the error indicates that ActiveSpreadSheet().getSheetByName(name) returns null. So please confirm whether there is the sheet name of name in the Spreadsheet, again. And in your case, ActiveSpreadSheet().getSheetByName(name).toString() returns Sheet for various values of name. So I think that at ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0, only when name is Sheet, it becomes false.
– Tanaike
Nov 13 '18 at 3:20

I've used this sheet and the functions flawlessly for almost 4 years now. I can post the full code-behind if necessary.
– Anders
Nov 13 '18 at 3:22

2

Thank you for replying. For example, how about creating new spreadsheet and copying the values, sheets and the script, and then, running the script? If that was not the solution, in order to confirm your situation, can you provide a sample spreadsheet including the script for replicating your issue? Of course, please remove your private information.
– Tanaike
Nov 13 '18 at 8:06

There are probably only a few of reasons why SpreadsheetApp.getActiveSpreadsheet() would fail. 1) The code is being run many times in rapid succession, and a quota limit is being hit 2) The script project is not bound to a Sheet 3) Google servers had a service outage. 4) Maybe the permissions changed? SpreadsheetApp.getActiveSpreadsheet() is used a lot. If there was a service outage on Google's servers, there would be at least thousands of people complaining that their code didn't work any more. Does anyone else have access to the Sheet? Have you removed an permissions recently?
– Sandy Good
Nov 13 '18 at 13:45

add a comment |

I wrote a function a while ago to determine if a sheet in a workbook already exists. Here is the function:

function SheetNameExists(name)
 return (ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0);

All of a sudden within the last month or so I get a TypeError: Cannot call method "toString" of null. (line 105, file "Code"). Line 105 is the return statement.

Up until this error started happening I have had no issues with any of the scripts on this Sheet. I did not change anything in the code-behind.

Did Google change up something without providing deprecation support?

EDIT: I forgot ActiveSpreadsheet() was not a built-in function when I wrote all this out. This is the code I wrote to get it:

function ActiveSpreadSheet()
 return SpreadsheetApp.getActiveSpreadsheet();

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

I think that the error indicates that ActiveSpreadSheet().getSheetByName(name) returns null. So please confirm whether there is the sheet name of name in the Spreadsheet, again. And in your case, ActiveSpreadSheet().getSheetByName(name).toString() returns Sheet for various values of name. So I think that at ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0, only when name is Sheet, it becomes false.
– Tanaike
Nov 13 '18 at 3:20

I've used this sheet and the functions flawlessly for almost 4 years now. I can post the full code-behind if necessary.
– Anders
Nov 13 '18 at 3:22

2

Thank you for replying. For example, how about creating new spreadsheet and copying the values, sheets and the script, and then, running the script? If that was not the solution, in order to confirm your situation, can you provide a sample spreadsheet including the script for replicating your issue? Of course, please remove your private information.
– Tanaike
Nov 13 '18 at 8:06

There are probably only a few of reasons why SpreadsheetApp.getActiveSpreadsheet() would fail. 1) The code is being run many times in rapid succession, and a quota limit is being hit 2) The script project is not bound to a Sheet 3) Google servers had a service outage. 4) Maybe the permissions changed? SpreadsheetApp.getActiveSpreadsheet() is used a lot. If there was a service outage on Google's servers, there would be at least thousands of people complaining that their code didn't work any more. Does anyone else have access to the Sheet? Have you removed an permissions recently?
– Sandy Good
Nov 13 '18 at 13:45

add a comment |

I wrote a function a while ago to determine if a sheet in a workbook already exists. Here is the function:

function SheetNameExists(name)
 return (ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0);

All of a sudden within the last month or so I get a TypeError: Cannot call method "toString" of null. (line 105, file "Code"). Line 105 is the return statement.

Up until this error started happening I have had no issues with any of the scripts on this Sheet. I did not change anything in the code-behind.

Did Google change up something without providing deprecation support?

EDIT: I forgot ActiveSpreadsheet() was not a built-in function when I wrote all this out. This is the code I wrote to get it:

function ActiveSpreadSheet()
 return SpreadsheetApp.getActiveSpreadsheet();

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

I wrote a function a while ago to determine if a sheet in a workbook already exists. Here is the function:

function SheetNameExists(name)
 return (ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0);

All of a sudden within the last month or so I get a TypeError: Cannot call method "toString" of null. (line 105, file "Code"). Line 105 is the return statement.

Up until this error started happening I have had no issues with any of the scripts on this Sheet. I did not change anything in the code-behind.

Did Google change up something without providing deprecation support?

EDIT: I forgot ActiveSpreadsheet() was not a built-in function when I wrote all this out. This is the code I wrote to get it:

function ActiveSpreadSheet()
 return SpreadsheetApp.getActiveSpreadsheet();

google-apps-script

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

edited Nov 13 '18 at 3:17

asked Nov 13 '18 at 2:59

Anders

5,2023084133

asked Nov 13 '18 at 2:59

Anders

5,2023084133

asked Nov 13 '18 at 2:59

Anders

5,2023084133

I think that the error indicates that ActiveSpreadSheet().getSheetByName(name) returns null. So please confirm whether there is the sheet name of name in the Spreadsheet, again. And in your case, ActiveSpreadSheet().getSheetByName(name).toString() returns Sheet for various values of name. So I think that at ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0, only when name is Sheet, it becomes false.
– Tanaike
Nov 13 '18 at 3:20

I've used this sheet and the functions flawlessly for almost 4 years now. I can post the full code-behind if necessary.
– Anders
Nov 13 '18 at 3:22

2

Thank you for replying. For example, how about creating new spreadsheet and copying the values, sheets and the script, and then, running the script? If that was not the solution, in order to confirm your situation, can you provide a sample spreadsheet including the script for replicating your issue? Of course, please remove your private information.
– Tanaike
Nov 13 '18 at 8:06

There are probably only a few of reasons why SpreadsheetApp.getActiveSpreadsheet() would fail. 1) The code is being run many times in rapid succession, and a quota limit is being hit 2) The script project is not bound to a Sheet 3) Google servers had a service outage. 4) Maybe the permissions changed? SpreadsheetApp.getActiveSpreadsheet() is used a lot. If there was a service outage on Google's servers, there would be at least thousands of people complaining that their code didn't work any more. Does anyone else have access to the Sheet? Have you removed an permissions recently?
– Sandy Good
Nov 13 '18 at 13:45

add a comment |

I think that the error indicates that ActiveSpreadSheet().getSheetByName(name) returns null. So please confirm whether there is the sheet name of name in the Spreadsheet, again. And in your case, ActiveSpreadSheet().getSheetByName(name).toString() returns Sheet for various values of name. So I think that at ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0, only when name is Sheet, it becomes false.
– Tanaike
Nov 13 '18 at 3:20

I've used this sheet and the functions flawlessly for almost 4 years now. I can post the full code-behind if necessary.
– Anders
Nov 13 '18 at 3:22

2

Thank you for replying. For example, how about creating new spreadsheet and copying the values, sheets and the script, and then, running the script? If that was not the solution, in order to confirm your situation, can you provide a sample spreadsheet including the script for replicating your issue? Of course, please remove your private information.
– Tanaike
Nov 13 '18 at 8:06

There are probably only a few of reasons why SpreadsheetApp.getActiveSpreadsheet() would fail. 1) The code is being run many times in rapid succession, and a quota limit is being hit 2) The script project is not bound to a Sheet 3) Google servers had a service outage. 4) Maybe the permissions changed? SpreadsheetApp.getActiveSpreadsheet() is used a lot. If there was a service outage on Google's servers, there would be at least thousands of people complaining that their code didn't work any more. Does anyone else have access to the Sheet? Have you removed an permissions recently?
– Sandy Good
Nov 13 '18 at 13:45

I think that the error indicates that ActiveSpreadSheet().getSheetByName(name) returns null. So please confirm whether there is the sheet name of name in the Spreadsheet, again. And in your case, ActiveSpreadSheet().getSheetByName(name).toString() returns Sheet for various values of name. So I think that at ActiveSpreadSheet().getSheetByName(name).toString().indexOf(name) != 0, only when name is Sheet, it becomes false.
– Tanaike
Nov 13 '18 at 3:20

I've used this sheet and the functions flawlessly for almost 4 years now. I can post the full code-behind if necessary.
– Anders
Nov 13 '18 at 3:22

Thank you for replying. For example, how about creating new spreadsheet and copying the values, sheets and the script, and then, running the script? If that was not the solution, in order to confirm your situation, can you provide a sample spreadsheet including the script for replicating your issue? Of course, please remove your private information.
– Tanaike
Nov 13 '18 at 8:06

There are probably only a few of reasons why SpreadsheetApp.getActiveSpreadsheet() would fail. 1) The code is being run many times in rapid succession, and a quota limit is being hit 2) The script project is not bound to a Sheet 3) Google servers had a service outage. 4) Maybe the permissions changed? SpreadsheetApp.getActiveSpreadsheet() is used a lot. If there was a service outage on Google's servers, there would be at least thousands of people complaining that their code didn't work any more. Does anyone else have access to the Sheet? Have you removed an permissions recently?
– Sandy Good
Nov 13 '18 at 13:45

add a comment |

1 Answer
1

active

oldest

votes

I question how this could have worked before. getSheetByName(name) will return a Sheet object. toString() simply returns the string "Sheet". So it will only match name if name is "Sheet". However your problem is quite simple.

var active = SpreadsheetApp.getActiveSpreadsheet(); // For sure the active spreadsheet
var sheet = active.getSheetByName(name); // Where name is a string representing a sheet name
if( sheet ) return true; // Sheet with name exists
return false; // Other wise sheet is null or does not exist

answered Nov 13 '18 at 15:46

TheWizEd

546147

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273153%2fall-of-a-sudden-getting-an-error-in-google-sheets-from-this-function-that-existe%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

var active = SpreadsheetApp.getActiveSpreadsheet(); // For sure the active spreadsheet
var sheet = active.getSheetByName(name); // Where name is a string representing a sheet name
if( sheet ) return true; // Sheet with name exists
return false; // Other wise sheet is null or does not exist

answered Nov 13 '18 at 15:46

TheWizEd

546147

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

add a comment |

var active = SpreadsheetApp.getActiveSpreadsheet(); // For sure the active spreadsheet
var sheet = active.getSheetByName(name); // Where name is a string representing a sheet name
if( sheet ) return true; // Sheet with name exists
return false; // Other wise sheet is null or does not exist

answered Nov 13 '18 at 15:46

TheWizEd

546147

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

add a comment |

var active = SpreadsheetApp.getActiveSpreadsheet(); // For sure the active spreadsheet
var sheet = active.getSheetByName(name); // Where name is a string representing a sheet name
if( sheet ) return true; // Sheet with name exists
return false; // Other wise sheet is null or does not exist

answered Nov 13 '18 at 15:46

TheWizEd

546147

var active = SpreadsheetApp.getActiveSpreadsheet(); // For sure the active spreadsheet
var sheet = active.getSheetByName(name); // Where name is a string representing a sheet name
if( sheet ) return true; // Sheet with name exists
return false; // Other wise sheet is null or does not exist

answered Nov 13 '18 at 15:46

TheWizEd

546147

answered Nov 13 '18 at 15:46

TheWizEd

546147

answered Nov 13 '18 at 15:46

TheWizEd

546147

answered Nov 13 '18 at 15:46

TheWizEd

546147

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

add a comment |

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

Thank you this worked! I changed the function that was getting an error to the following: function SheetNameExists(name) return ActiveSheetByName(name); . The ActiveSheetByName() function just does what your second line does. Thanks again!
– Anders
Nov 25 '18 at 4:06

I don't know of any method ActiveSheetByName. It must be a function you have in your project. My method is vanilla SpreadsheetApp.
– TheWizEd
Nov 25 '18 at 12:49

I guess I forgot to mention that I made a custom function I put in my code so I did not have to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); since I called that several times. My fault.
– Anders
Nov 29 '18 at 4:05

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj