Wondering if any of you wizards have any ideas for simple ways to do Base64 encoding of uploaded images (needed for Cloudinary image layering). Currently, I have a script writing in an encoding formula. It’s really long and calculation heavy. It’s already showing signs that it’s going to start acting weird as more rows pile up. Lately the last row of that particular column is going blank randomly (causing Glide to not see it and breaking my Cloudinary link), and then when a new row of data is added, the formula magically appears in that cell again. Some strange sheets bug I guess… likely caused by slow calculation or something.
I’ve been considering switching the encoding to happen exclusively in the script using Utilities.base64encode(), but the problem with that is if someone edits and replaces the image… I’ll need to create some kind of ‘for’ loop to check that entire column of data every time there’s a change just to make sure I don’t need to swap out the old encode with an encoded version of the new image.
And I’m wracking my brain trying to think whether there’s an easier way. Has anyone come up with a simpler solution for encoding and storing base64 info in a corresponding column and then changing it if the image changes in the other column?
Have you tried writing a function in script using Utilities.base64encode(), import it into the sheet as a macro and using it in an array formula for that column?
I have. Here’s the problem I hit with that. About 1 In 10 times, glide pulled in the word “Loading…” which is what you see for a moment when a macro is loading a script function… and then it got stuck with that for a while In Glide until it got unstuck… eventually. Don’t know what jiggled it, but no reloading the sheet or refreshing the glide builder would unstick it. And in the interim, links were broken. So I gave up on that, thinking it was the least reliable. Maybe I should give it another go.
I haven’t. I’ve been encoding then manually and entering a static base 64 per image. Would be nice if Google had a formula…
I know right?! I use their UTF formula with other strings for Cloudinary. Might also be nice if Cloudinary came up with another formatting for layered URLs than base64.
I can’t input the encoded data manually, because I’m layering images users are uploading. So it has to be dynamic.
Not an arrayformula…but I think this crazy long formula is accurate:
I can go further and say, I know it’s accurate. It’s the very formula I’m currently using. [hat tip Joshua!].
The problem is it’s very very calculation intensive, and as rows pile up, this particular formula (being used in a few different sheets and columns) is really working my whole spreadsheet. And it now seems to be causing problems as described above. Formula just randomly disappearing in the last cell, and Glide pulls in blank data, then magically reappearing seconds later. His scripting version is great too… but the problem there is what I described with glide occasionally pulling in the word “Loading…” rather than the results of the function… which… I guess wasn’t finished loading. What’s more, refreshing Glide wouldn’t replace that Loading… with the correct data. Some quirk that may have been resolved by now… but still, it’s not reliable if 1 out of ten times, Glide pulls in the data before that function has finished putting in the encoded data.
Still going the scripting/macro route may be my best bet at this point. It’s the least intensive on the sheet. I haven’t tested to see if data is changed in the corresponding cell whether it triggers a reload of the formula again. When I was using it, it only loaded when the function was first placed in the cell, and never loaded again. That would be a problem, since I need these to auto update if the image is changed.
I wonder if I could set up a Zap to do something. Seems like such a waste of Zaps, though.
Oh, I know! Glide! How about a Base64Encode column or feature?! I kid I kid… no, actually I really want that. lol.
Maybe something Glide can solve? Like we can convert dates to pretty format, convert images to Base64?
Probably REALLY low on their priority list 
1 Like
Of course, the other option is a script that pulls in the two columns in question as an array, does a ‘for loop’ check on every base64 encoded version stored to see if it matches a newly encoded version of the currently stored image, and if it doesn’t it overwrites the old version with the new version. Wondering what the script speed on that would be if the sheet was say, 25k rows in length. Each iteration of the loop would encode the current image of that row, then compare that to what’s stored in the encode column for that row, and if different would replace that array variable before going to the next loop iteration. Once it’s finished the full loop, it would use setValues() to reset every value in that column in one call. That last call would be pretty fast, even if writing in 25k cells of data… just wondering about all of those loop iterations and encoding each time to do the comparison.
1 Like
Would it help if you duplicated the sheet (minus the base64 column, ran the script against the duplicated sheet, then pulled the value back into the first sheet? It may prevent the missing formula or loading issue. I do something like this in one of my apps, but with two separate spreadsheets entirely. Using Importrange, I pull in key sheets into a second spreadsheet that does the heavy formulas and scripting, then the original spreadsheet pulls the calculated values back using importrange. It has definitely improved the response time in the app as far as usability. The calculated values displayed elsewhere still take several minutes to come back into glide after the second import, but this prevents weird issue I used to have with Glide. I used to lose data (student lessons) because my spreadsheet took too long to calculate (billing and invoicing) and wouldn’t accept additional lessons being rapid fire entered from the app. At some point the additional data got lost (probably due to a resync between glide and the sheet). It still does weird things occasionally, as far as recalculating multiple times and temporarily losing calculated billing amounts (never understood why the sheets are doing this), but it works itself out after awhile. I have an “As of” date and time in the app so the user is aware if calculations are currently occurring and when it’s finished, so that helps. I’m still working to make my formulas more efficient so I can merge everything into one spreadsheet again, but I just haven’t had the time to sit down and do it. Anyway…you could try a similar method in in spreadsheet to see if it helps.
Whoa… this method of doing heavy calculation in a second sheet and using importrange to bring in the values and keep the Glide linked sheet light as a feather (well maybe an ostrich feather), is just friggin brilliant.
I’m going to have to play with this. Very cool.
And yeah, I have a few processes (check for in-game currency balance updating) that Glide doesn’t pull in on the first pass and need to wait for either another edit or the 3 minutes until the next auto-sheet collection. But my app doesn’t demand super fast updating… it has a blockchainy quality and that kind of thing mimics the long wait times folks have to suffer when transactions are sent.
I’m way more concerned with finding lighter solutions for all the different gamified aspects I’ve added to this. I fear the app may be reaching its limit with the new features I want to add, simply because sheets isn’t fast enough.
Also, @Jeff_Hager there are so many weird quirks in Sheets I can’t for the life of me figure out. Like for example script failures I get randomly thoughout the day. No reason I can see. Everything is functioning normally. Scripts doesn’t even say what the problem was. Just that a script couldn’t complete. Likely some weird hiccup caused by a Rand or Now updating in the sheet while the script is running. No idea. And yeah, weird stuff like data loss with no explanation. Then reappearing. Not syncing to Glide, eventually resolving itself.
To be honest, a lot of it started when scripts was upgraded to V8. Literally the next day, I was fighting all kinds of weirdness and alerts.
I haven’t gone down the path of upgrading to v8 yet. Or at least it hasn’t presented itself as a problem yet. Most of my heavy calculations involve queries and sheet formulas on a couple thousand lines and the inability to arrayformula some of them. @George_b gave me some solutions, but I haven’t implemented them yet. You and I had a little bit have back and forth on this awhile back in this thread (Looking for advanced google sheet formulas).
The second sheet has been a lifesaver and for the most part is a neat little trick to lighten up the glide sheet. My biggest beef with google sheets are the formulas that don’t work with arrayformulas. I kind of get why they don’t but in my head it seems like things would be so much simpler if google had a formula that could do some of these functions. Even though I write code for a living, I still try to avoid doing scripting as much as I can to keep things simple. The built in functions seem so much faster than using a script.
I’ve gotten occasional script failures also, or I’ve hit a max computing limit for the day, even though my main script does little more than copy a name from one sheet to a few others. The thing that sucks is I don’t get notified about errors until the next day, so it’s harder to diagnose the problem. Yeah, those little quirks are another reason I try to avoid scripting.
I have my failure notifications set for immediately in the script’s trigger settings. Maybe that’s why I feel like they’re a bit excessive.
And my scripts were auto-updated to v8. Kept asking me if I wanted to upgrade, and then one day it was like, “you’re using the new V8” and I was like, “umm… okay… thanks?”
I will say that I’ve found that array formulas do tend to slow down a sheet over time. More than the same formula in the cell without the array. But I hear you. In most cases they are way way way faster and more preferable to a script.
And there are some things that really could be arrayformulas in sheets that aren’t and it’s pretty frustrating. Some stuff I can see why, but stuff like SUMIFS… why can’t that be an arrayformula?? Especially given the SUMIF cheat you can do to (mostly) simulate SUMIFS.
1 Like
Hehe, yep! I guess that’s the price we pay for free. We get what we get.
1 Like
Ugh I hear you. REALLY wish filter() worked with arrayformula()
2 Likes