menu
announcement

Spectrum is now read-only. Learn more about the decision in our official announcement.

Glide

🚨🚨🚨 The Glide community forum has moved! Please click the new link below:

Channels
Team

I missed the caution about using IMPORTRANGE

May 28, 2019 at 4:22pm

I missed the caution about using IMPORTRANGE

May 28, 2019 at 4:22pm (Edited 3 years ago)
I'm writing an app that others are going to be required to be changing content. In order to prevent them from screwing up the main application spreadsheet I decided to use a separate spreadsheet for each section of data. The idea was that I would give the 3 individuals/committees (Social Committee, Membership Committee, etc.) access to these stand alone spreadsheets and use the IMPORTRANGE function to pull the the data into, non visible to the app tabs. I then used mostly arrayformulas to pull, and format as I needed, this raw data over to that actual tab sheets linked to the app navigation pages. It worked great, or so I thought.... UNTIL I noticed refresh issues. Since I was deep into development and had the main spreadsheet open I didn't notice it at first but as the application became more stable and moved to Beta things started to not show up. I missed this caution https://docs.glideapps.com/all/reference/using-sheets/importrange
Anyway I had to rethink how I could still protect the main sheet. My thoughts are that I will need to write Google scripts and possibly use triggers to push or pull the data between the sheets. Does anyone have any other ideas on how to sync them up? Maybe there is a plan in the works to allow more than one Google Sheet to be connected to a given app?
Edit: Below is the Google Script function I wrote and use to sync up two Sheets. Note that first it removes all data in the target sheet, then populates it with the source data. You can either push from the linked Sheet or pull into the Sheet linked to your app. All you have to do is create a timed trigger, create an add on menu item, or a button on the Spreadsheet you have this code in to get it to execute.
function cloneHomeData() {
var sourceId = "1c6ZpgVd3v98As........";
var sourceSheetName = "Sheet1";
var target = SpreadsheetApp.getActiveSpreadsheet();
var targetId = target.getId();
var targetSheetName = "homedata";
CloneSheet(sourceId, sourceSheetName, targetId, targetSheetName );
}
//========================
//========= clone a sheet
function CloneSheet(sourceId, sourceSheetName, targetId, targetSheetName) {
var sourceSS = SpreadsheetApp.openById(sourceId);
var sourceSheet = sourceSS.getSheetByName(sourceSheetName);
//Get full range of data
var sourceRange = sourceSheet.getDataRange();
//get A1 notation identifying the range
var sourceA1Range = sourceRange.getA1Notation();
//get the data values in range
var sourceData = sourceRange.getValues();
var targetSS = SpreadsheetApp.openById(targetId);
var targetSheet = targetSS.getSheetByName(targetSheetName);
// clear all data from target sheet
clearRange(targetSheet);
//set the target range to the values of the source data
targetSheet.getRange(sourceA1Range).setValues(sourceData);
}
// pass in a sheet reference
function clearRange(sheet) {
var dataRange = sheet.getDataRange();
dataRange.clearContent();
}

May 28, 2019 at 5:42pm
I use Apps Script to essentially do the importing for me. Granted, it does take some time for the pasted values to sync on the app platform itself.
For my app, I am using multiple google
sheets in a similar manner as I believe that you are, but I’m not having the issue. True, there can be a delay in refresh of up to a few minutes, but I haven’t had issues of not refreshing at all. Are you just seeing a delay? Or no refresh at all?
If you are using IMPORTRANGE, as long as you have the Spreadsheet open in your browser the app will refresh in the 3 min timeframe. In my case the update of the linked file happens overnight via a trigger of a script that pulls the calendar appointments from the Google Calendar. Since the main spreadsheet attached to the app is not open in a browser that spreadsheet does not update with the new data from the linked via IMPORTRANGE file. In my case since the main spreadsheet is only open in a browser when I'm making tweaks to the program I had long lags (multiple hours) in the refresh. If you look at the link in my original post the Glide team warns you of this.

June 12, 2019 at 9:38am
Hi George, You seem to be a bit more skilled than I am in Scripting. I was trying to find a way to use a button in Glide to trigger an update (in this case an import from Calendar) for the Reservation app I'm working on. Do you know of a way to do this? I'm looking for a refresh/update without the user going into the spreadsheet to run the app script. I've tried Web Apps but no success so far. Any suggestions/hints for me to explore? Kind regards,
You can set up the script to trigger on edit actually! So if your user is inputting data to the sheet, ideally it would work. I can post a script here soon for you to use!
like-fill
1
Hi Megann, I'll look forward to that. I have on change working as a trigger but not On Edit, so I'd be interested in seeing your code. In the app I'm looking to build (from latest import from Calendar, which is where I'm storing "reservations" for the app)I want to pull down latest data and view prior to adding any new input. The person would look up existing reservations , then if slot free add a new reservation. I currently trigger the update/refresh at the tailend of an input function, but that is not the solution I'm looking for. I want to trigger from a button in glide and only way I have found so far to trigger an app script (not time based) externally from the spreadsheet is from an App Script Web App. I may need to do a bit more research on that. Kind regards,
You need to set up a trigger, an installable trigger: see this for some info https://webapps.stackexchange.com/questions/119684/diference-between-onedit-or-onchange-trigger
What I did was create a new sheettab with a description of the action and a true false cell next to them: https://gyazo.com/0564f8ea0b1e89525cba8c190c5dd209
So when I toggle the switch the trigger I created (I used onChange, for some reason I had issues with onEdit) would run.
function checkFlags(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell;
cell = ss.getRange("Utils!B2");
if (cell.getValue()) {
cloneDataToMontebelloMobile();
cell.setValue(!cell.getValue());
}
cell = ss.getRange("Utils!B3");
if (cell.getValue()) {
// TODO - add function call
cell.setValue(!cell.getValue());
}
cell = ss.getRange("Utils!B4");
if (cell.getValue()) {
// TODO - add function call
cell.setValue(!cell.getValue());
}
cell = ss.getRange("Utils!B5");
if (cell.getValue()) {
// TODO - add function call
cell.setValue(!cell.getValue());
}
}
Edited
In my case I have created another app that has it's own table, not the same one as the main app. This "Administration" app mirrors the info in the other main app table. So the administrator can make changes to the data in the mirror tables and when it all looks good he can flip one of these switches which would trigger the function that copies the new data to the main spreadsheet. That is what the function cloneDataToMontebelloMobile does. I'll have to create a video of what I'm doing as it's a little hard to explain. Let me know if you have questions.
Here is a picture of what the trigger looks like in my Apps Script: https://gyazo.com/5eb289050df1387628f472b275df7947

June 12, 2019 at 8:39pm
Hi George. You are the man!! Once you explained about the switch it seemed so obvious. A simple elegant solution. Thank you for sharing. I do like your way of resolving the ImportRange issue. One of my demo apps breaks a bit with people putting data in, so I like the idea of them using a different file. It allows you to be able to put some data validation processes in place before using it.
Thank you for other links to Stack overflow. That is helpful. I think I've been missing the event trigger in my functions. I appreciate you taking the time to assist me. Kind regards,

June 13, 2019 at 3:01am
Glad to hear my efforts are of help to someone. BTW as an example for you and others, my app spreadsheet has an extra sheettab for every tab enabled in the app. So for my Directory Icon tabsheet, I have the raw data in a tab called dataDirectory. In my case there are columns for a street number, streetname, address line 2, city, state, zip as that is the way it is stored in a totally different table. That table data is maintained by someone else and is copied/cloned into this data tabsheet (either by a user runing the clone script from a Addon Menu click, or a triggered overnight push that runs the same function). So.... the actual Directory tabsheet, that is enabled in the app, has a column called Address. I use an ArrayFormula on row 2 to concatinate those 5 separate columns into an address column, that is used for display and mapping purposes within the app.
Edited

June 27, 2019 at 2:10am
I've been banging my head against the wall on this one. I have the switch, then I have code: function checkFlags(){ var app = SpreadsheetApp; var shtToGet = app.getActiveSpreadsheet().getSheetByName("import"); var cell = shtToGet.getRange("E1").getValue(); //Logger.log(cell); if (cell="TRUE") { getEvents(); SpreadsheetApp.getActiveSpreadsheet().toast("My message to the end user","checkFlags() activated"); } }
And I have triggers (I've tried onChange, onEdit and Time Based (1 minute) separately and together. If I'm in the spreadsheet and change switch from False to True, it activates, if I do it from either the Desktop glideapp or mobile device it does not. Have you any advice? I did a quick loom video to show you the issue https://www.loom.com/share/364d11dbacbb490c9f44547e334e0367
I'd appreciate any help you can offer, kind regards
You have to set up a timed trigger that Google App runs. As you have discovered if you aren't interacting with the Spreadsheet yourself the onChange, onEdit, etc. will not be triggered. Take a look at this GIF. It will show you where to click in your script and what will come up. Then Add a Trigger, select your checkFlags as the function to run then the rest you should be able to figure out. https://gyazo.com/74c7e6c3b27074f1626c7ae88f952a18
I guess I should have looked at your video first. I think it's just your expectation of how fast it happens. Remember that Glide can take up to 5 min to sync and then you have your 1 min, timer. Click the switch and go get a tea. Also have the checkFlags code set the value of that cell to False as the last step in the function so you can see that it did run. Again in my case it can take up to 3 or 4 min from when I flip the switch until it runs the timed function.
Edited
Similar to what you did when pointing the script to your Import sheet, maybe try adding another column in Sheet1 with a formula that looks at the boolean True/False value from the checkbox and then fills the cell with Yes/No or any other text values you want. Then change your script to look at the new column for a value of Yes instead of a boolean True/False.
But it's working if he does it on the sheet . His video shows the code executing when he clicks the checkbox.
I'm saying that he should try to see if the script works with a non-boolean value. His Import sheet was pulling the value from Sheet1, but in either case it's a boolean. Using a formula in another column that will fill with "YES" if True or "NO" if False may or may not work. I'm wondering if the script doesn't play well with boolean values when it's set from the app. I have no idea if it will work, but it's something different to try.
Show more messages