Need help: How to dynamically change which Sheet is active in script

26 views
Skip to first unread message

James Workman

unread,
Nov 26, 2022, 1:18:29 PM11/26/22
to Google Apps Script Community
Hello, I'm a bit stuck trying to figure out how to properly implement the getSheetByName function. Here's my code currently 

var sheet = ss.getSheetByName("Sheet1");
var lastRow = sheet.getLastRow();
sheet.getRange(lastRow+1,5).activate().setValue(textBody + camCheck);

This works fine but if for some reason I wanted to change the "Sheet1" to something else it won't work. Initially I tried to create a variable that uses .getName() for the currently active sheet but .getSheetByName(); wont accept anything other than a string for some reason? Anyone encounter this? Why does google expect me to hard code a string into the getSheetByName()?

CBMServices Web

unread,
Nov 26, 2022, 1:25:02 PM11/26/22
to google-apps-sc...@googlegroups.com
You don't really need to use getSheetByName at all unless you wish to do something with a specific sheet.

Use getActiveSheet and then you can do what you want with it.

Explain a little more of what capability you are trying to build and we can give you some suggestions.


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b178eadc-6330-4b36-a970-4de33ca757f5n%40googlegroups.com.

James Workman

unread,
Nov 26, 2022, 1:34:54 PM11/26/22
to Google Apps Script Community
The spreadsheet I am working off of has about 10 sheets/pages.

The script is supposed to take user input from a gui and input it into the currently viewed sheet/page's next available cell. 

The issue I am encountering is that I am trying to activate the currently viewed sheet/page using .getSheetByName() but the desired sheet will change dynamically. I can only get it to work when I hard code a string such "Sheet1" - but this is subject to change. I tried assigning a variable such as var sheetName = ss.getName() of the currently viewed sheet/page and then pass it into ss.getSheetByName(sheetName); but it won't accept any variables, only strings AFAIK - Any advice or work arounds?

CBMServices Web

unread,
Nov 26, 2022, 1:45:39 PM11/26/22
to google-apps-sc...@googlegroups.com
Yeah in this case, you don't need to use getSheetByName at all.

Use this:

var sheet = SpreadsheetApp.getActiveSheet();



James Workman

unread,
Nov 26, 2022, 1:52:33 PM11/26/22
to Google Apps Script Community
That worked!

At the top of my script I define these vars:
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var acvSheet = ss.getActiveSheet();

So in previous attempts when I tried calling acvSheet in a similar fashion as you described in your response I was redundantly looping the ss var which was throwing errors my way. redefining var sheet = SpreadsheetApp.getActiveSheet(); worked due to it no longer looping redundantly. Thanks for assistance I am a total noob LOL I knew I was approaching this incorrectly. 

CBMServices Web

unread,
Nov 26, 2022, 1:57:13 PM11/26/22
to google-apps-sc...@googlegroups.com
No problem. Glad to be of help.

Reply all
Reply to author
Forward
0 new messages