CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gleb
Gleb

Reputation: 215

Is there any reliable alternative to ImportRange() in new google spreadsheets?

I have importrange() from about 20 different sheets in my new google sheets. After some time the formula brakes, and shows "Error: Loading data... ", and it wouldn't work until I change anything in the parameters, i.e. changing "Sheet1!a3:Ag" to "Sheet1!a3:AG", then it works for a few days, and then brakes again with the same error.

I've tried myImportrange custom function, like here Using the myImportRange custom spreadsheet function - TypeError: Cannot call method "split" of undefined But eventually received the same error.

Upvotes: 1

Views: 12273

Answers (3)

Brian
Brian

Reputation: 66

This is an old question, but since it's still a top result on Google here are some tips.

Too many IMPORTRANGE() functions in a sheet cause it to run slow and may give errors like you're describing. The hard limit is 50 cross-workbook reference formulas, but you'll likely bog down well before that if you're importing a lot of cells. To work around this, you can set up a spreadsheet that imports data from several sources into 1 sheet that acts like a data hub, then import that range to your final spreadsheet. It makes tracing issues a bit harder, but reduces the number of IMPORTRANGE() functions. Put some forethought into organization too, to make changes easier later, such as leaving extra columns and rows between import ranges.

I also tried Preactive's code and it worked well for a while. I set it on a 5 minute trigger and set it up to replace 10 IMPORTRANGE() functions accessing different spreadsheets. However, a seemingly unrelated issue I ran into was my Google Forms would intermittently stop pushing data to their linked spreadsheets, even though responses were still being received by the forms. This would happen to 1-2 different forms about once a week. The form data would backfill 12-24 hours later. I don't know if the code running every 5 minutes exceeded my daily runtime limit or caused some other issue, but the code itself never gave any errors. I deleted the trigger and switched back to the IMPORTRANGE() formulas and I've had no issues with the Forms since.

Using the Gscript workaround may be good for sheets that only need to be updated a few times a day or on-demand, but for data that needs to be updated more continuously, IMPORTRANGE() is the best option right now.

Upvotes: 2

Preactive
Preactive

Reputation: 310

I have improved it sense then:

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//Script Based ImportRange

//Example importRange_('0AodPsg..................','Main4NS','A:G','Common','C7','y')
//Explanation importRange_('Importing Spreadsheet Key or URL','Importing Spreadsheet Tab Name','Importing Spreadsheet Tab's Range','Destination Spreadsheet Tab Name','Destination Spreadsheet Tab's placement','Will add note to the first cell of import')

function importRange_(Source_Key,Source_Sheet,Source_Range,Set_Sheet,Set_Pos,Add_Note) 
{
  var SourceTypeCheck = Source_Key.indexOf("https://"); 
  if(SourceTypeCheck >= 0)
  {
    var Load = SpreadsheetApp.openByUrl(Source_Key).getSheetByName(Source_Sheet).getRange(Source_Range).getValues();
    var Name = SpreadsheetApp.openByUrl(Source_Key).getName();
  }
  if(SourceTypeCheck == -1)
  {
    var Load = SpreadsheetApp.openById(Source_Key).getSheetByName(Source_Sheet).getRange(Source_Range).getValues();
    var Name = SpreadsheetApp.openById(Source_Key).getName();
  }
  var RowVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getRow();
  var ColVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getColumn();
  if(Add_Note.toUpperCase() == 'Y')
  {
    SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,1,1).setNote("Import Script Updated On: " + Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy hh:mm a")+"\nSS Name: "+Name+"\nRange: "+Source_Sheet+"!"+Source_Range+"\nSS Key: "+ Source_Key);      
  }
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,Load.length,Load[0].length).setValues(Load);
  SpreadsheetApp.flush();
  SpreadsheetApp.getActiveSpreadsheet().toast('At: '+Set_Sheet+'!'+Set_Pos,'Import Completed:');
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

Upvotes: 1

Preactive
Preactive

Reputation: 310

function UpdateRange() {
var values = SpreadsheetApp.openById('Key').
  getSheetByName('Sheet1').getRange('A3:G').getValues();
SpreadsheetApp.getActive().getSheetByName('data').
  getRange(1,1,values.length,values[0].length).setValues(values);
}

Then you can add timers to this function

Upvotes: 0

Related Questions