CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1464409
user1464409

Reputation: 1042

Is it possible to do ImportRange in Google Apps Script?

I have been playing around with Google Apps Script today and I am trying to code some custom spreadsheet functions. I have done some searching but cannot find an answer to my query.

I know that on a Google Spreadsheet you can use ImportRange in a cell on a spreadsheet like this:

=ImportRange(spreadsheet_key;sheet!range_of_cells)

My questions are is it possible to do something similar in a Google Apps Script and if so, how?

I want to import a range of cells from a sheet on another spreadsheet (not a sheet on the spreadsheet where the script will reside).

Upvotes: 8

Views: 43743

Answers (3)

illmortem
illmortem

Reputation: 103

It seems Google, in their infinite wisdom, has altered the behavior of openById and similar functions. They are no longer allowed in the context of custom functions.

See https://code.google.com/p/google-apps-script-issues/issues/detail?id=5174 for more details.

They suggest using IMPORTRANGE as a workaround, but as previously mentioned, this needs to be called within a cell.

Our solution was to use IMPORTRANGE in the sheet, and pass the acquired data into our custom function, as the data set was small. I hope this information helps!

Upvotes: 3

Ben
Ben

Reputation: 42283

I needed to do this recently. This is what I came up with, simply hard-coding the spreadsheet key and range into ahab's myImportRange function

// to be used in the spreadsheet like so:  = myScriptedImportRange( GoogleClock() )
// no need to include key or range because they are in the script here
// 
// the third parameter - GoogleClock() - triggers an automatic update every minute.
// updated 2011-07-17 (ahab): better regex to strip sheetname of *outer* single quotes
// updated 2013-01-27 (ben) to hard-code key and range 
function myScriptedImportRange(  ) { 
 var key = "PUT YOUR DATA_SPREADSHEET_ID IN HERE"
 var sheetrange = "PUT YOUR SHEET AND CELL RANGE IN HERE"
 var shra = sheetrange.split("!") ;
 if (shra.length==1) shra[1]=shra[0], shra[0]="";  

 var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace( /'/g , "") ; updated 2011-07-17 (ahab)
 var rangestring = shra[1] 

 var source = SpreadsheetApp.openById( key )    
 if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
 else sheet = source.getSheetByName( sheetstring ) ;

 return  sheet.getRange( rangestring ).getValues(); 
}

In my case I have a set of private sheets, an intermediate sheet that uses the regular myImportRange and VMERGE with some SQL to combine selections from the private sheets into the intermediate sheet, and then a public sheet that simply has one cell containing = myScriptedImportRange( GoogleClock() )

Note that there is a similar approach here: https://stackoverflow.com/a/11857014

Note also that the ImportRange function and related functions often have a problem of not displaying the imported data when the origin workbook(s) is/are not open. A simple way around this has been described in a comment here: https://stackoverflow.com/a/11786797

Upvotes: 1

Serge insas
Serge insas

Reputation: 46794

Yes, this is perfectly possible. You just need to call SpreadsheetApp.openById and then get the desired sheet and ranges normally.

Please take a look at the documentation : range.getValues() and range.setValues() are very basic GAS methods and are pretty well described.

Read the tutorial as well.

Upvotes: 8

Related Questions