Original Question: I have multiple tabs in a Google Spreadsheet that represent different data sources. Currently, I have a variable (var = quote1location) that is equal to the sheet name that I would like to get my data from based on other logic.
Pretend that quote1location can equal 'Sheet1', 'Sheet2', or 'Sheet3' depending on the logic but for this case, it equals 'Sheet1'.
var totalpeople = quote1location.getRange('A1').getValue();
In the function above, Apps Script will return an error saying 'quote1location.getRange is not a function' because Apps Script is not substituting the value of the variable that I have designated ('Sheet1') but is using the variable name ('quote1location' instead. I would like Apps Script to process this as 'Sheet1.getRange('A1').getValue()'.
Your help would be appreciated
Answer: Thank you all for your responses. What I was trying to do is use a string in the 'getRange()' function. Pretend I had two Google Sheets named 'Sheet1' and 'Sheet2' and I had a variable that helped me determine what sheet to grab as my data reference. I was trying to set a variable as either (var source = 1) or (var source = 2) so that I could then use this variable in my getRange() function like this: ('Sheet' + source).getRange('A1').getValue();
What I was trying to do here is if var source = 1, then I would get my data from 'Sheet1'. If the var source = 2, then I would get my data from 'Sheet2'.
The issue (as mentioned by those who responded) is that I was trying to use .getRange() on a string, not an object like a specific spreadsheet. Instead of using var source = 2 or var source = 1 I should be using
ss = SpreadSheetApp.getActiveSpreadsheet()
if(somevariable = somecondition){
var source = ss.getSheetbyName('Sheet1')
}
if(someothervariable = someothercondition){
var source = ss.getSheetByName('Sheet2')
}
Now when I use getRange()' on 'source', it will be calling the sheet that I have designated rather than trying to retrieve a range from a string which will not work.
Thank you very much to all who provided feedback.
I believe you are looking to do:
const quote1Location = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName(`Sheet1`)
const totalPeople = quote1Location.getRange(`A1`).getValue()
Alternatively:
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
const quote1Location = `Sheet1`
const totalPeople = spreadsheet.getSheetByName(quote1Location)
.getRange(`A1`)
.getValue()
Whether these are the exact syntax you're hoping to use or not, I hope this helps you better understand how to accomplish accessing a Sheet.