Fighting with IMPORTRANGE

We all love Google Sheets, right?

We know also the pain with IMPORT… functions. They are not stable.

Photo by Chromatograph on Unsplash

This article describes how to create your own ImportRange function and control it. Why you may want it? Well, imagine you want to importrange from 10th of Spreadsheets/Sheets, and your formulas are a bit sloooo…

The typical project contains at least these 2 types of files:

1. 🧙🏼‍♂️Master
2. 🍫Front ( or User or Work ).

The first type stands for reference tables, common for your business. It has the source of truth for your clients, employees, products, etc.

The second type of sheet is the production sheet, which basically is used by your staff to operate your business.

Ok, we need to connect those two!

One way to connect things is IMPORTRANGE formulas. The other is to use helper Add-Ons to do IMPORTRANGE for you. An alternative way is to use app scripts to achieve that.

➡️Code

You will need a Web-App deployed. Here’s a sample code for your addon:

function doGet() {

  // Any code goes here to grab the data you need
  var ss = SpreadsheetApp.openById('Master-File-ID'); // CHANGE !!!
  var s = ss.getSheetByName('Master-Important-Shetet'); // CHANGE !!!
  var r = s.getRange('A3:F21'); // CHANGE !!!
  var d = r.getValues();
  // the result is 2D array of data, as ususal

  var text = JSON.stringify(d)
  return ContentService.createTextOutput(text);
}

function doPost() { return doGet(); }

↑ Test this as a Web App, deploy it to execute as you and to run from anywhere.

In your 🍫Front make a script like that:

function getWebApp() {
  var url = 'Your-Web-App-URL/exec'; // CHANGE !!!
  var fetch = UrlFetchApp.fetch(url);

  var d = webAppContentText2Data_(fetch.getContent());

  return d;
}

/** webAppContentText2Data_
 * Not that obvious code to convert Web-App's
 *    `ContentService.createTextOutput(text);`
 * to the real data behind that
 */
var webAppContentText2Data_ = function(bytesData) {
  var text = Utilities.newBlob(bytesData).getDataAsString();
  return JSON.parse(text);
}

Use!

Use it as a custom function. Wow! We now have a function to get some results from another Spreadsheet, like IMPORTRANGE, but it does not refresh until you ask to. See that checkbox in A1? Use it to refresh values.

✅TODOS

Here are a few thoughts to make this even better:

1. Add Cache Service to your WebApp. Do not force the script to use getRange, or getValues each time.
2. Add more Spreadsheets and Sheets to the call. Yes, do more “heavy” calculations with a script, to let your formulas take a rest.
3. Split the WebApp and Logic. In order not to create new versions of your WebApp, make a Library and connect it to the WebApp. This will let you change the library in the Development mode and not touch the WebApp.
4. Add some parameters. Yes, WebAbb can have parameters, so you may make it as smart as you want.

↑ Web App code using library

Have an idea? Comment

And you’re always welcome to my 🏠: https://www.cooltables.online