Is there a working version of ImportRange as a script?

Question

Is there a working script for importrange (google sheets)? I have done a ton of research and tried scripting one myself and google app scripts just seem to not take the following function:

SpreadsheetApp.openById( key )

According to my research openById is no longer supported and this function has been altered.

Please let me know what I can do to improve on this script and see if there's anything I'm missing.

(EDIT) I want to import a sheet named "Data", from my Service Area Test Spreadsheet to a sheet called "Import Here" from my Test Development Spreadsheet.

function ExportRange() {
  var destination = SpreadsheetApp.openById(DESTINATION SPREADSHEET ID HERE);
  var destinationSheet = destination.getActiveSheet();      
  var destinationCell = destinationSheet.getRange(SET DESTINATION CELL EXAMPLE:"A1");
  var cellData = '=IMPORTRANGE(origin spreadsheet_key, range_string)';
  destinationCell.setValue(cellData);
}

Based on the script above, this is how I inserted everything.

function ExportRange() {
  var destination = SpreadsheetApp.openById(ID from "Import Here" here);
  var destinationSheet = destination.getActiveSheet();      
  var destinationCell = destinationSheet.getRange("A1"); //Dropping "Data" starting from "A1"
  var cellData = '=IMPORTRANGE(ID from "Data" here, "A1:L1")'; // My range string is "A1:L1"
  destinationCell.setValue(cellData);
}

"According to my research openById is no longer supported" Do you have a source for this? — Tedinoz
Hello @Tedinoz,nice to hear from you again. Here is the link. stackoverflow.com/questions/11143684/… As I was reading into this thread, it lead me to more discussions and people having suggestions to just use an API, however, seeing this is part of a work project, I don't have that kind of back up at work to get an API. So trying to learn to script it myself. — FeatherDuster

Andres Duarte Andres Duarte · Accepted Answer · 2019-07-25T07:38:14

openByID function is supported and working as normal [1]. Be aware that the ID is the code in the spreadsheet's URL and it's something like this "1F45t1mfewf0Zum0AHV43gIl3KEXTvsb3hnNkh29bdsQfS".

I tested your code and worked without problem. Make sure you're passing the SpreadSheet ID and Range parameters as strings (enclosed in "") in the openById and getRange functions, probably this is causing the problem.

Also the formula won't work if you don't previously set the spreadsheetID and range in the cellData string.

[1] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)

Is there a working version of ImportRange as a script?

1 Answers