CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Peter Hochgraf
Peter Hochgraf

Reputation: 11

How do I speed up Sheet load times with a lot of importranges?

I've been promoted into developing & maintaining a Google Sheets database at work. I know very little about Google Sheets scripting & from asking around, and researching it's looking like GAS is probably the avenue that I need to start heading down.

So we have 3 Workbooks in Google Sheets; 2 contain large amounts of data, the other workbook provides a UI for our sales dpt. to access the data. I really wish I could share these with you, as describing them is difficult. In the UI workbook, separate pages are paired with sheets in the one database (lets call it database A).

A salesman will go to the UI sheet for the product he's selling a device for; the top section of the sheet allows him to select, essentially, a row from database A. After the selection is made, the rest of the sheet is populated with products we manufacture that work with the choice made in the top section; the products we make are stored in the other database ("B"). We have to have two databases, as we've earlier hit the cell-limit in sheets with the two databases combined.

On average each UI page has about 150 Importranges. Looking up done with Query.

Our problem is that this UI is getting pretty slow, initial load time makes it worthless for salesmen on the road, and annoying to the salesmen here in the office. The delay when making the initial selections (querying database A) is usable, but still much slower then we'd like. And we're not finished building UI pages.

I've seen online that most people recommend using Apps Script to replace importrange, but knowing nothing about Apps Script, I haven't been able to understand what is being done, or mainly how to take the apps script and actually put the data in the cells.

So I'd appreciate any help I could get in speeding this up.

Upvotes: 1

Views: 3874

Answers (2)

Cooper
Cooper

Reputation: 64042

First let me say that the Google Apps script documentation has improved greatly over the years and I find it pretty easy to use now. If you open up a code editor in Google Sheets and go to Help menu and select API reference then that links you up to just about everything you need to know. If you go to the Google Apps reference for spreadsheets and look at the SpreadsheetApp object you'll see that there's three commands to open up another Spreadsheet not a sheet but a Spreadsheet. You can do it by file, by id or by URL.

If you click on the Url command it will take you to an example like this:

// The code below opens a spreadsheet using its id and logs the name for it.
 // Note that the spreadsheet is NOT physically opened on the client side.
 // It is opened on the server only (for modification by the script).
 var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/abc1234567/edit');
 Logger.log(ss.getName());

As it points out, it doesn't actually open the file on the client-side it just opens it up on the server. So it may be necessary for you to open them up manually at first just to get an idea of what they look like. Once you know how they are organized then you can use the open command to get a Spreadsheet Object and from it select a specific sheet and then a data range. Once you have a range then you can load an array like this.

var myArray = rng.getValues();

This will load the entire range in one fell swoop into a JavaScript array and of course it would be nice if you can filter out unwanted data from the array and then put it into your current sheet at a desired range. Note that the range sizes have to be exact matches and also please realize that ranges start from 1 and arrays start from 0 so that can cause you some grief. Also let me add a few caveats that I've run into.

If your dealing with a one row or one column range array then you have to get the array's in the correct form. I tried writing them here but the Stack Overflow text converter keeps messing them up so I'd recommend you go to my reference on that issue here.

If you've coded in JavaScript in the past I'm guessing that you'll have no problem coming up to speed with Google Apps Scripting with the new documentation and an occasional visit to Stack Overflow for a question or two. I've gotten some great answers here from other users. If you need a JavaScript reference here's one that I use.

Upvotes: 1

Oli Trussell
Oli Trussell

Reputation: 1

You are probably best off using a WebApp served from Google Apps Script for the UI which I'd be happy to help with if you had some sample data. If you wanted to still use the sheets, then you could replace the importRanges with some Google Apps Script function that runs every 10 minutes or so to keep the UI sheet updated. It should speed up load times. Something like this would work for you:

function importSheetA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var database = SpreadsheetApp.openByUrl("DATABASE_A_URL");
var dataToCopy = database.getSheetByName("DATABASE_A_SHEET_NAME").getDataRange().getValues();
var copyToSheet = ss.getSheetByName("UI_SHEET_NAME");
var copyData = copyToSheet.clearContents().getRange(1, 1, dataToCopy.length, dataToCopy[0].length).setValues(dataToCopy);
}

Upvotes: 0

Related Questions