Is it possible to do ImportRange in Google Apps Script?

importrange google sheets
google sheets importrange from same sheet
importrange adding permissions
importdata google sheets
importrange from excel to google sheets
google script to copy and paste to another sheet
google apps script copy sheet
setformula google 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).

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.

How can I do ImportRange with Google Apps Script?, I would revise Ruben's broad steps as: Get the destination spreadsheet. Get the destination cell from the correct sheet. SetValue with the  I have a Google Apps Script that compares information submitted in a Google form to information that is brought into the Google spreadsheet using the IMPORTRANGE function. The script works perfectly if I run it manually but if it runs from the form submit trigger it reports that it isn't getting the correct information from the cells using

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!

Replace ImportRange w/ Script - Copy row from , I have been using ImportRange to copy the master data into 5 different sheets and We're getting too busy to do this and I know I can automate it using scripts but I editing them to fit my spreadsheet's parameters, but have not been able to​  However, I'm trying to find a way to do this via a script because I'm creating many spreadsheets, each with a query-importrange function (I 'own' the spreadsheet which has data to import). There's too many for me to manually 'allow access' via the pop up and update the function to include the query function.

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

IMPORTRANGE - Docs Editors Help, Imports a range of cells from a specified spreadsheet. Sample Usage. IMPORTRANGE(" https://docs.google.com/spreadsheets/d/abcd123abcd123 ", "​sheet1! The Importrange Google Sheets function is the only way, using Sheets’ built-in functionality, to integrate data between spreadsheets. For a person experienced with spreadsheets it’s an easy formula to use, but it can cause confusion for those without. Below I’ve described its applications, how to implement it and its good and bad points.

How to sync data using Google Apps Script in Google Sheets , Using Google Apps Script, you can import data into your Google Sheet the IMPORTRANGE() function in Google Sheets to import data form a source If you want to read the full blog post on how to do one-way syncing, check better, and I feel like I'm finally able to get my money's worth out of the Mac. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source.

Equivalent of IMPORTRANGE with script in Google Sheets, Equivalent of IMPORTRANGE with script in Google Sheets [closed] as expected, do you know how I could express e.g the following function with a script? Possible duplicate of Small Edit on Import Range Script for Mass Edits on Google  You can do this easily using the IMPORTRANGE function in Google Sheets. While it’s really easy to connect two workbooks in Excel that reside on your hard drive or a network drive, the only way to connect two Google Sheets is by using the IMPORTRANGE Function. IMPORTRANGE Function – Syntax IMPORTRANGE (spreadsheet_url, range_string)

Google Sheets Functions – IMPORTRANGE, Google Sheets has a wonderfully useful function called IMPORTRANGE. That's normal. Now all we need to do is add our IMPORTRANGE functions. Drive, Forms, Sheets, Docs, Slides, Sheet Functions, and Apps Script:. 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.

Comments
  • I'm aware of these functions but I don't see how I can tell it to pick the range from another spreadsheet. Maybe I'm just being stupid again.
  • Hi Serge, I took the liberty to edit your answer to address his doubt on the comment :)
  • @Henrique : thanks for the 'edit', you're perfectly right, I still have some progress to do on some answers formatting and 'completeness' I'll take care of that ;-)
  • Thanks Serge and Henrique, I see how to do it now. Great help once again.
  • Google Apps Script
  • The Google clock function no longer works in the New Google Sheets and it does not let you put the alternative function "NOW()" into the main function within the sheet. It just keeps saying loading and nothing happens.