Geek asks and answers

Is it possible to do ImportRange in Google Apps Script?

Today I play with Google Apps Script, and I'm trying to encode some custom spreadsheet functions. I did a few searches, but can not find the answer to my request.

I know that in a Google spreadsheet you can use ImportRange in a cell in a spreadsheet as follows:

=ImportRange(spreadsheet_key;sheet!range_of_cells)

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

I want to import a range of cells from a sheet into another table (and not a sheet in the spreadsheet where the Script will be located).

+5
source share
3 answers

, . SpreadsheetApp.openById, .

, : range.getValues() range.setValues() - GAS .

.

+5

, Google openById . .

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

IMPORTRANGE , , , .

IMPORTRANGE , . , !

+2

. , , ahab myImportRange

// 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(); 
}

, , myImportRange VMERGE SQL , , , = myScriptedImportRange( GoogleClock() )

, : fooobar.com/questions/1117493/...

Note also that a function ImportRangeand related functions often have a problem not displaying the imported data when the source book (s) is not open / not open. A simple way about this was described in a comment here: fooobar.com/questions/1117494 / ...

+1
source

More articles:


All Articles