How to reference different tabs in google sheets with the Script Editor?

Question

So I am working on a project where I have a google doc that connects to a spreadsheet, and I added two other sheets that I want to populate with information from the first spreadsheet. I want to do this in the Script Editor but I am having trouble referencing the other tabbed sheets. This is what I've tried.

var tabs = [
  'Sign In',
  'Master Info',
  'Made Sheet'  
];

var i = 0;
var sheet = ss.getSheetByName(tabs[i]);
var range = sheet.getRange(1, 1).getValues();
Logger.log(sheet);
Logger.log(range);

The outputs I get are:

sheet
Time Stamp

"Time stamp" is the first cell in the sheet so I can reference the value but not the name of the sheet or any other index that isn't i = 0; Any help is appreciated, or sources on how the tabbed sheets can interact.

This is what I've done so far and am confused as to why I get null for the second sheet even though the name can be printed and it is populated with data.

  var sheet = ss.getSheetByName(tabs[0]);
  //sign in sheet variable

  var sheet1 = ss.getSheetByName(tabs[1]);

  Logger.log(tabs[0]);//works
  Logger.log(tabs[1]);//works

  var A1Value = sheet.getRange(1,1).getValue();

  var signIn = A1Value;//gets first cell


  var secondVar = sheet1.getRange(1,1).getValue();//error occurs

When you have an array of values and you want to do something with each of those values, you need to use a loop. Spend some time reviewing JavaScript language fundamentals on a site like the Mozilla Developer Network (MDN), and doing various tutorials. — tehhowch

Cooper Cooper · Accepted Answer · 2020-01-22T23:43:26

If you working in a Google Doc then you can access a spreadsheet with

var ss=SpreadsheetApp.openById('ssid');//you supply the ssid

you can access any of the sheets by name with var sheet=ss.getSheetByName('SheetName');

and you can get values with var A1value = sheet.getRange(1,1).getValue();

and you can write to cells with sheet.getRange(1,1).setValue('new value');

If you know the order of the sheets and no one ever moves them then you can do something like this.

var shts=ss.getSheets();
var mostLeftSheet=shts[0];
var nextSheetToTheRight=shts[1];
var thetenthfromRight=shts[9];

If you can put all of the data that you wish access in the same block of cells then you can access them with getValues() and setValues() which return and set arrays of data. This is a much faster way of getting and setting data. Although, it's often difficult when dealing with spreadsheets that have lots of formulas and the data that you want is spread out in various places.

Try this:

var tabs = ['Sign In','Master Info','Made Sheet']; 
var sheet = ss.getSheetByName(tabs[0]); 
var sheet1 = ss.getSheetByName(tabs[1]); 
Logger.log(tabs[0]);//works 
Logger.log(sheet1.getName());//works 
var A1Value = sheet.getRange(1,1).getValue(); 
var signIn = A1Value;//gets first cell 
var secondVar=sheet1.getRange(1,1).getValue();//error occurs

How to reference different tabs in google sheets with the Script Editor?

1 Answers