I run a business with close to 50k clients. However, my apps generally focus on a subset of those customers (100 to < 4000).
My first problem is that I do Vlookups to find a clients lastname, firstname, etc. based on a client’s ID.
Obviously I can’t add the 50k database as I would immediately reach my row limit.
So my first feature request would be to be able to mark a sheet as “Not Accessible” in Glide so that none of the data in that sheet would be accessible in Glide or generate any update. Glide would just totally ignore that sheet, but other sheets in the file could still access the data through Google Sheets formulas (vlookup, filter, etc.).
Anyway, since this is not possible today, I wrote a Vlookup function in Google Script, that updates my Glide accessible sheets, while having my 50k contact list reside in a different file altogether.
This, although not really real-time, works like a charm !
But, there is a little caveat… Since the update is made through a Google Script, Glide doesn’t realize that an update occurred in the sheet. Good news, this can be addressed with the Data Sync Extra setting…
Problem with this is that Glide says it occurs every few minutes. That’s very vague. So let’s assume it happens every 5 minutes. That means 8.6k updates per month for just one app !!!
That doesn’t leave much room for anything else…
So my 2nd feature request is for us developers to be able to set the frequency of Data Sync Extra as needed for our app. I could perfectly live with an update every 30 min to an hour…
Do you guys agree or see other ways to handle such issues ?
Actually, you can.
As long as those extra rows aren’t referenced anywhere in your Glide App, they won’t be counted as part of your row quota.
I run about 6-8 Apps & Pages projects off a single Google Spreadsheet data source that contains about 80k rows. Each of those projects has its own independent row count, depending on which sheets are referenced. One of them has about 35k rows, the rest have less than 20k rows.
That’s not necessarily true. Those extra updates only occur if the app is actively being used. If there are no active users for a period of time, then glide will not perform any extra updates.
Cool, I hadn’t understood that… So your saying that if none of the columns in a sheet are referenced in Glide, then Glide will not count the rows in that sheet ???
Is that true even if I make changes in that sheet in Google Sheets ?
Also, in my experience, an import from another spreadsheet has allowed for Google to send updates to Glide. I used run two spreadsheets in my app. One for the app, and one to offload some complex calculations that took a while to run. The second spreadsheet would import certain data from the first spreadsheet, allowing it to run it’s calculations, and then the first spreadsheet would import those results. I didn’t use any scripting. This seemed to work with and without using the ‘extra updates’ feature. Sometimes it would take several minutes, but those results would eventually show up in the app.
Yes, if you do an ImportRange() the update is recognized, but I can tell you by experience that when I change data in the sheet through a Google script that Glide isn’t made aware of any changes.
However, I see a middle ground solution with @Darren_Murphy comment… I probably don’t want to have the whole 50k users in my Glide Database, but I could create a sheet that lists all the unique IDs which is updated through Google script, and then a vlookup on that sheet to get the client’s info should trigger an update ?
Yes, if a sheet tab is not used in any way by the app itself, then it will not apply to your row count.
However, I’m not really sure if changes to that unused sheet will trigger an update in glide. I’m thinking that it’s possible that it could, since I believe it’s actually google that is sending the data to glide, but Google will send the first update immediately, and subsequent updates evey few minutes. So if you are making several changes to a sheet, Google will begin to batch them after the first update.
That’s definitely something to try. I’m not sure if google would still send the update or not.
Well, I can confirm that a Google Sheets Vlookup does NOT trigger an update.
So while @Darren_Murphy’s comment clarified that my No1 feature request was in fact already handled.
I also just realized that changing manually a cell in a sheet that is not addressed by Glide will also not trigger an update…
So my 2nd feature request stands, as it would be unmanageable to have my full client database in every Google sheets file, and I would have to create a Glide relation anyway to lookup the client data, which would in turn count all those rows…
@Jeff_Hager Your solution with the ImportRange wouldn’t work either, because an update on a sheet that is not addressed by Glide doesn’t seem to generate an update in Glide. So unless I address that sheet in Glide with a relation and lookups, I won’t get any updates, and if I address it in Glide, I face the row count issue…
*Below is an idea I wrote up earlier today, before I got busy with other stuff, but now I have doubts that it will work. I explain further at the end of this post. I still wanted to share it though.
So, here’s a thought that’s a little more in line with what I had done in my app:
For reference, I will refer to the table your app uses as the AppCustomer table. I will refer to your 50k client list as the Client50 table.
What I would do is create an import in your Client50 spreadsheet that will import all of the data from the AppCustomer table into the Client50 spreadsheet. Then create a new Vlookup column in that duplicate AppCustomer table that retrieves the information that you need from the Client50 list. Then go back into your original spreadsheet and do an import to pull back name, etc from the duplicate AppCustomer table in the Client50 spreadsheet.
So essentially, you are moving all of your data from the original spreadsheet to a second spreadsheet, letting the VLOOKUP’s populate, then moving that Vlookup data back to the original spreadsheet.
All I know is that this method had seemed to work regardless if I had extra syncs enabled or not. The second spreadsheet did the heavy lifting, and the imports still worked to move data back and forth while still syncing with glide as needed. Now, I could be talking out of my rear end, but I seem to recall that was the case a couple of years ago, when I was trying to restructure and speed up some things in my app. I have now mostly removed the functionality that required the second spreadsheet, but I do have a small remnant that pulls and shows a date from that second spreadsheet. Unfortunately, that part doesn’t appear to be working when I have extra syncs turned off. With extra sync’s turned off, it now appears that the value is no longer updating. So maybe it was a placebo where I thought it was working in the past (but really wasn’t), or maybe glide changed some things with their back end since then.
So, I’m not sure what to tell you. If it’s not working now, then I’m not sure what else you could do short of having some sort of action that would cause an update and a subsequent resync with the google sheet.
Thanks @Jeff_Hager, I had actually had the same idea. I imported all my unique IDs from my AppCustomer table into a sheet in the file where the Client50 table resides. I then vlookup all the data from the Client50 table. In my AppCustomer table I then ImportRange that data back.
Didn’t seem to work at all, but then I realized that this evening here, my app doesn’t get any updates at all from my Google sheets even when I manually change a cell. I waited over an hour and still no update at all.
So I’m guessing there’s some kind of problem with Glide updates going on tonight. I’ll test again tomorrow…
Another brute force idea I had was to just have a fake importrange in my app. Since Google updates that about every 30 min, that would be sort of my slow homemade extra sync.
But again, I need to wait until tomorrow to see if that works too…
I have several scripts that are constantly moving data in and out of my master Google Spreadsheet, and in some cases replacing the entire contents of tables on an hourly basis. I’ve never had any issues with data syncing (or not), but maybe I just haven’t looked closely enough. I do have Extra data sync enabled, and at least one of the connected Apps has active users on it 24/7, so maybe that in combination with extra sync mode is enough to keep Glide in sync with the Google Spreadsheet.
You can buy more updates – you don’t have to worry about “leaving room” for more. If you have 50k clients a month, is paying another $200 a month for more updates too expensive?
Hi @david, The 50k customers is a database of consumer customers, not active all the time. If my apps were addressing that many clients regularly, I could see your point of view.
The problem is that a duo of apps (admin & staff accessing the same 2k customers) already get me in trouble with limits although we have an enterprise account.
As stated the 50k is an issue we can work around, but 13k syncs in a single app in less than a month, because of extra sync turned on, is just crazy. Notwithstanding that updates, as opposed to rows are counted globally and not on a per app basis.
I was planning to build several apps to manage aspects of the business and interact with customers, but this indicates that I probably will have to reconsider Glide as an option, as it will become way to onerous.
