How to import data from a different workbook, with a button. getSheetById(id) doesn't work

Question

I want to import data from 3 sheets in a different workbook, in the same drive, to a newer workbook, with the press of a button.

Each workbooks has a sheet called 'Log Wizard' with 2 boxes. The bottom box displays the current spreadsheet key. The top box is an input field to paste an existing sheet key into.

https://docs.google.com/spreadsheets/d/1833-5CnOurssETuZFtohiHRRBx1V3_95_L0S-f9vPLk/edit#gid=187106606

enter image description here

enter image description here

So far, I cannot figure out how to make a script that does the following:

• When the button is pressed, get the source sheet id which is provided by the user in cell M11 of the current sheet, and use that to access the referenced spreadsheet

• And then copy 3 different ranges from three different sheets (2 of which are hidden) to empty sheets of the same names in the newer workbook (vehicle configuration, budget log and log).

I know that getSheetById has issues with permissions of some sort. I am not very familiar with google scripting so any help is appreciated.

//variables
var id = "123456789abcdefg";
var sheet = "LOG WIZARD";
var cells = "M11:AB13";
var range = SpreadsheetApp.openById(id).getSheetByName(sheet).getRange(cells);
var id = range.getSheet().getParent().getId();

//custom function to import logs
function importlogs() {

//Source sheet from which to import from
var is = SpreadsheetApp.openById(id)
var sheet1i = is.getSheetByName("BUDGET LOG");
var sheet2i = is.getSheetByName("LOG");

//Current sheet from which to export to
var xs = SpreadsheetApp.getActiveSpreadsheet();
var sheet1x = xs.getSheetByName("BUDGET LOG");
var sheet2x = xs.getSheetByName("LOG");

//Copy and paste contents of import Budget Log sheet to export Budget Log sheet
sheet1i.getRange("A3:AO").copyTo(sheet1x.getRange(sheet1x.getLastRow()+1,1,1,7), {contentsOnly:true});

//Copy and paste contents of import Log sheet to export Log sheet
sheet2i.getRange("A3:O").copyTo(sheet2x.getRange(sheet2x.getLastRow()+1,1,1,7), {contentsOnly:true});

}

I am unsure if it is actually passing the key correctly or at all. I tend to get the error Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp. or Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 5).

I assume this is because of some type of permission that doesn't allow a custom function to call another spreadsheet?

I have tried various suggestions here: You do not have permission to call openById

and here: How to use an installable trigger to get SpreadsheetApp.openById(id) to work, by calling it from a cell

but so far nothing I have found works.

Answer 1

The //variables section is in the global scope of the script project, so those definitions get evaluated every time any function runs, just before the function executes.

The problem is that those definitions are not compatible with the custom functions you are using. A custom function runs in a limited context where methods that require authorization are not available. When SpreadsheetApp.openById(id) gets called in that context, the custom function errors out, even though the problematic line of code is not within the function itself.

The importlogs() function is run through a button. It will run in an authorized context where SpreadsheetApp.openById(id) is available normally.

To solve the problem, delete the //variables section. The same declarations are repeated within importlogs(), so that should be enough. If you want to improve the importlogs() function, use the code given to you in your previous question.

You may want to add this code to easily call the importLogs_() function through a button:

/**
* Call this function through a button.
* https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets
*/
function runImportLogs() {
  const sourceSsId = SpreadsheetApp.getActive()
    .getRange('LOG WIZARD!M11')
    .getDisplayValue();
  importLogs_(sourceSsId);
}