Google App Script + Google Sheet - Fetch URL from cell (which links to separate sheet) and pull in sheet tab from linked sheet

0
votes

I've been working/researching this for a few days and get close, but need to add an element of automation, my current scripts are not suitable for the end goal.

I need to:

  • Grab a URL from a cell on my primary "generator" sheet.
  • The URL points to a completely separate Google Sheet.
  • I need to grab a specific named sheet tab, from the externally linked Google Sheet, and bring it into my master doc, as a new sheet tab.
  • The source cell with the URL, may be replaced with a new link, hence the automation required. I do not want to have multiple scripts with hard-coded sheet IDs or URLs.
  • The URL link may change, but each external sheet will have a tab with the same name, so that remains consistent.

It seems if I store the URL cell (B28 in this case contains the URL) as a variable in the script, it no longer becomes readable. EG:

  var SPREADSHEET_URL_REFERENCE_SOURCE_SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calc Generator");
  var SPREADSHEET_URL_REFERENCE_SOURCE_RANGE = SPREADSHEET_URL_REFERENCE_SOURCE_SHEET.getRange("B28");
  var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValues();

Afterwards my script can no longer grab the URL. Any help would be appreciated, I'm a little snow blind at this point!

2
urlgoogle-apps-scriptgoogle-sheetshyperlink
Can I ask you about your question? 1. I cannot understand about Afterwards my script can no longer grab the URL.. Can you explain about the detail of it? 2. If Afterwards my script can no longer grab the URL. is your issue and you want to resolve it, what is I need to:? Are we required to prepare the script achieving all of I need to:? – Tanaike
Hi! No: I have the rest of the script ready, it was just an issue resolving the URL as a variable. :) – AndrewC

2 Answers

0
votes

Change this line:

var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValues();

to this 

var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValue();

In other words remove the s at the end. If you don't then the value you need becomes SPREADSHEET_URL_VALUE[0][0] Even though it's just one cell it's still a two dimensional array.

0
votes

I think I have figured this out. It will fetch a URL of an external sheet, from a cell on a specific sheet of your own, and pull in the external sheet to yours and create a new tab copy of it.

As Cooper said, setting getValue helped, but then I can use openByURL and point to my link variable to grab it, this allows grabbing of specific external sheet tabs.

Thank you for the help!

    function copySheettest()
{
  //  
  var SPREADSHEET_URL_REFERENCE_SOURCE_SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("[MASTER] Calc Generator");
  var SPREADSHEET_URL_REFERENCE_SOURCE_RANGE = SPREADSHEET_URL_REFERENCE_SOURCE_SHEET.getRange("B28");
  var SPREADSHEET_URL_VALUE = SPREADSHEET_URL_REFERENCE_SOURCE_RANGE.getValue();
  Logger.log(SPREADSHEET_URL_REFERENCE_SOURCE_RANGE);



  var sourceSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL_VALUE);
  var source_sheet = sourceSheet.getSheetByName("Info Calculations");
  var target = SpreadsheetApp.getActiveSpreadsheet();
  var target_sheet = target.getSheetByName("target_sheet");
  var source_range = source_sheet.getRange("A1:AA69");
  var target_range = target_sheet.getRange("A1:AA69");
  //
  var values = source_range.getValues();
  var bGcolors = source_range.getBackgrounds();
  var colors = source_range.getFontColors();
  var fontSizes = source_range.getFontSizes();
  // make use of autocomplete to find other methods...
  target_range.setValues(values);
  target_range.setBackgrounds(bGcolors);
  target_range.setFontColors(colors);
  target_range.setFontSizes(fontSizes);
}