How do I get a google sheet name based on cell value using Google Apps Script?

I have a workbook (Google Sheets) with a sheet per user (n users) and a master sheet (1) and in my script I need to get the current sheet name using a constant or variable so it works for every user -> sheet.

I have the sheet name on every user's sheets, on cell B2, using the following code:

function sheetName() {return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();}  

I have a submit button that takes B1, D9 and B6 values for the current user's sheet to update the master sheet, but when trying to use use the sheetname as a variable it doesnt work, most probably because I am missing something minor. The only way it works is to hard code the sheet's name but since it has to be dynamic this doesnt work for me.

One example of many user's sheet name is "user5"

This is part of my code, and ways I tried to do this are commented:

    1   var testId = ["B6","A"];      2   var testStatus = ["D9","Q"];      3   var cellUpdateDate = ["B1","P"];      4   function submitStatus() {      5     var ss = SpreadsheetApp.getActiveSpreadsheet();       //var formSS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();        //var ss = SpreadsheetApp.getActiveSpreadsheet();        //var form_sheet_name = ["B2","M"];        //var formSS = ss.getSheetByName(form_sheet_name);        //var formSS = ss.getActiveSheet().getName();        //var formSS = ss.getRange("B2").getValues();        //var formSS = ss.getRange('B2').getValues();        //var formSS = ss.getSheetByName(B2);      6    var formSS = ss.getSheetByName("user5");      7    var datasheet = ss.getSheetByName("Test Cases");  

As you can see I tried almost everything. 6 only works when I type "user5", but cant figure it out to make it variable.

Thank you!!!, cheers

https://stackoverflow.com/questions/66977814/how-do-i-get-a-google-sheet-name-based-on-cell-value-using-google-apps-script April 07, 2021 at 07:38AM