CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gabriel
Gabriel

Reputation: 19

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

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

Upvotes: 0

Views: 2942

Answers (1)

Cooper
Cooper

Reputation: 64040

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

Upvotes: 1

Related Questions