-1

I've been trying to build a script which I can use to switch from a given sheet in one spreadsheet, to a given sheet in another spreadsheet, in the same browser tab, where both spreadsheets are on the same google drive and in the same folder.

The purpose for doing this is I have more data than I can fit on a single google sheets spreadsheet (More than 200 individual sheets are needed) and I'd like to be able to navigate between them as seamlessly as possible.

I could have sworn I had a simple script like this working at some point, but I'm not sure. The script does appear to run, but it there is no result, it doesn't actually open the other sheet.

I've tried using both .openByID and .openByUrl, and both appear to give me the same result.

I set up two new spreadsheets, A and B, each with their own script, A pointing to B, and B pointing to A. The destination sheet within the spreadsheet has been named MainA and MainB.

//This is your "Go Home" Button
//URL of A: https://docs.google.com/spreadsheets  /d/1LzsxZ3cCcOltELM_0T4VwipqYv5BbBDj9ugAZcciNHQ/edit#gid=0
//URL of B: https://docs.google.com/spreadsheets/d/1wrjr9VSsHb63RKz87JQbe20mCo1CteKHtsAiUGrb6O0/edit#gid=0
//ID of A: 1LzsxZ3cCcOltELM_0T4VwipqYv5BbBDj9ugAZcciNHQ
//ID of B: 1wrjr9VSsHb63RKz87JQbe20mCo1CteKHtsAiUGrb6O0
function goto_a() {
  var ss = SpreadsheetApp.openById("1LzsxZ3cCcOltELM_0T4VwipqYv5BbBDj9ugAZcciNHQ")
  ss.setActiveSheet(ss.getSheetByName("MainA"));
}

Obv the script for A is similar to this one, but with differently named function, destination sheet, and spreadsheet ID

In both cases, I'm using a button (drawing image) with the relevant script attached. If need be, I can share these spreadsheets so that the script can be tested.

Any advice would be great, thanks in advance.

Agent
  • 123
  • 2
  • 9
  • I don’t think there’s a limit on the number of tabs I think it is only a limit on the total number of cells. And I don’t believe scripts can open up spreadsheets in the same way that users can – Cooper Dec 10 '18 at 07:09
  • Gsheets has a limit of 200 tabs and 5m cells per workbook, 256 columns per sheet. I've definitely gotten there before. – Agent Dec 10 '18 at 07:26
  • Thanks I didn’t know that – Cooper Dec 10 '18 at 08:22
  • If I had that much data I’d probably be using your data base – Cooper Dec 10 '18 at 08:23

1 Answers1

0

As written in the official documentation ,

//Note that the spreadsheet is NOT physically opened on the client side. // It is opened on the server only (for modification by the script).

You can alternatively provide a link using Browser.msgBox() for the user to click.

Related Question

TheMaster
  • 45,448
  • 6
  • 62
  • 85