Cloudinary - combining upload and fetched images (base64 encoding?)

[Mark_Turrell]

Hi! I want to use a nice Template column in my Glide sheet to have a lovely image appear. I have a Cloudinary account and am having great fun (now) manipulating images.

BUT I am a bit stuck trying to combine together two images when one or both are external (i.e. needing to use Fetch).

For instance:

• this is only using Upload (images stored on Cloudinary in my account)

But what I would like to do is have the flexibility to include both stored (Upload) and external (Fetch) images via templates. This would be used, for instance, in the ‘Ask Card’ screenshot.

I googled about … and this is all I could find (and did not really understand, or could make it practical). Overlay an image that's taken from a fetched public URL – Cloudinary Support

Any tips greatly appreciated! Thanks

[Mark_Turrell]

I see that there are some threads on base64 encoding (I started from ‘cloudinary’ and am figuring this out in real time). Ideas for Base64Encode of images - #7 by John_Cabrera

Some things to try, nothing that easy. I might need to rethink my approach!

[Jeff_Hager]

I’m no expert on cloudinary, but there are several threads by @Robert_Petitto and @Krivo explaining some of it and their use of cloudinary’s fetch command. I’m not sure if it need base64 encoding, or just url encoding, but the Construct URL column will do url encoding I believe.

[Krivo]

@Mark_Turrell

If you create a google script then you can base64 encode the url to fetch. I put in a function for you to use in a google script

function Encode64ForMe (url) {

      var encodedVar = Utilities.base64EncodeWebSafe(url,Utilities.Charset.UTF_8);

      return encodedVar;

    }

In your google sheet you put in your url to fetch in a cell - e.g.

=Encode64ForMe(“https://kristianvoigt.dk/SouthAmerica800x600/photos/photo1.jpg”)

Create an url with a overlay which is fetched - it could look something like this

https://res.cloudinary.com/kristianvoigt/image/upload/f_jpg/l_fetch:aHR0cHM6Ly9rcmlzdGlhbnZvaWd0LmRrL1NvdXRoQW1lcmljYTgwMHg2MDAvcGhvdG9zL3Bob3RvMS5qcGc=/image%20carousel/USA.jpg

[Mark_Turrell]

Magic! Thanks - all working now

(my next task @Krivo is to make your image carousel )

[Krivo]

@Mark_Turrell all the info should be here

Image carousel - indication of mutiple images

Url: https://imagecarouselv2.glideapp.io/ [image] The image carousel functionality has changed so the image number overlay disappears after approx 2 seconds. That doesn’t work for me as the user might not see the number overlay - and thereby not realize that the image is actualy part of an image carousel - resulting in the user not using the image carousel. In the app there has been put an indicator in the button of the screen so the user can see that there are more images to se…

[ThinhDinh]

Not sure if yours work in an array, but I have been using this to mimic an arrayformula experience. Hope it helps.

Google Sheets - Quick Start with Base64 - With and Without Scripting

[Krivo]

@ThinhDinh That’s is a crazy formula!
Do you think it would be possible in Glide sheet - and would you need Google sheets?

[ThinhDinh]

Still needs Google Sheets for this, but hopefully we get a base64 encoding column some time in the future, to combine it with the new URL generator column.

[Krivo]

@ThinhDinh That would be nice - wondering when as I suppose core Glide functionality would be first.

[Robert_Petitto]

@Krivo

If you create a google script then you can base64 encode the url to fetch. I put in a function for you to use in a google script

function Encode64ForMe (url) {

      var encodedVar = Utilities.base64EncodeWebSafe(url,Utilities.Charset.UTF_8);

      return encodedVar;

    }

In your google sheet you put in your url to fetch in a cell - e.g.

=Encode64ForMe(“https://kristianvoigt.dk/SouthAmerica800x600/photos/photo1.jpg”)

@Krivo This is awesome! Is there a way to populate this automatically when new information comes in / new rows are created? I imagine it would be a part of the script to watch for new rows?

[Robert_Petitto]

@ThinhDinh

but hopefully we get a base64 encoding column some time in the future

@mark @jason?

[Krivo]

@Robert_Petitto Well, I you can include an extra arrayformula in the formula in below link (as @ThinhDinh provided) then I suppose you might be able to do without script. I haven’t been able to do the trick - but arrayformula wizards might

Google Sheets - Quick Start with Base64 - With and Without Scripting

(just needed to paste this formula as it is crazy

Please let us know if you get this to work with arrayformula

=CONCATENATE(JOIN(“”,ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))),REPT(“0”,(FLOOR((LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8)))))),“(.{6})”,“$1/”),“/”),“000000”)),0,“A”,1,“B”,2,“C”,3,“D”,4,“E”,5,“F”,6,“G”,7,“H”,8,“I”,9,“J”,10,“K”,11,“L”,12,“M”,13,“N”,14,“O”,15,“P”,16,“Q”,17,“R”,18,“S”,19,“T”,20,“U”,21,“V”,22,“W”,23,“X”,24,“Y”,25,“Z”,26,“a”,27,“b”,28,“c”,29,“d”,30,“e”,31,“f”,32,“g”,33,“h”,34,“i”,35,“j”,36,“k”,37,“l”,38,“m”,39,“n”,40,“o”,41,“p”,42,“q”,43,“r”,44,“s”,45,“t”,46,“u”,47,“v”,48,“w”,49,“x”,50,“y”,51,“z”,52,“0”,53,“1”,54,“2”,55,“3”,56,“4”,57,“5”,58,“6”,59,“7”,60,“8”,61,“9”,62,“-”,63,““,64,”=“))),REPT(”=“,(FLOOR((LEN(JOIN(”“,ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN(”“,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&”“,”(?s)(.{1})“,”$1"&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))),REPT(“0”,(FLOOR((LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8)))))),“(.{6})”,“$1/”),“/”),“000000”)),0,“A”,1,“B”,2,“C”,3,“D”,4,“E”,5,“F”,6,“G”,7,“H”,8,“I”,9,“J”,10,“K”,11,“L”,12,“M”,13,“N”,14,“O”,15,“P”,16,“Q”,17,“R”,18,“S”,19,“T”,20,“U”,21,“V”,22,“W”,23,“X”,24,“Y”,25,“Z”,26,“a”,27,“b”,28,“c”,29,“d”,30,“e”,31,“f”,32,“g”,33,“h”,34,“i”,35,“j”,36,“k”,37,“l”,38,“m”,39,“n”,40,“o”,41,“p”,42,“q”,43,“r”,44,“s”,45,“t”,46,“u”,47,“v”,48,“w”,49,“x”,50,“y”,51,“z”,52,“0”,53,“1”,54,“2”,55,“3”,56,“4”,57,“5”,58,“6”,59,“7”,60,“8”,61,“9”,62,“-”,63,"”,64,“=”))))+(4-1))/4)*4)-LEN(JOIN(“”,ARRAYFORMULA(SWITCH(BIN2DEC(TEXT(SPLIT(REGEXREPLACE(CONCATENATE(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))),REPT(“0”,(FLOOR((LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8))))+(6-1))/6)*6)-LEN(JOIN(“”,ARRAYFORMULA(BASE(UNICODE(SPLIT(REGEXREPLACE(REGEXREPLACE(A2&“”,“(?s)(.{1})”,“$1”&CHAR(127)),“'”,“‘’”),CHAR(127))),2,8)))))),“(.{6})”,“$1/”),“/”),“000000”)),0,“A”,1,“B”,2,“C”,3,“D”,4,“E”,5,“F”,6,“G”,7,“H”,8,“I”,9,“J”,10,“K”,11,“L”,12,“M”,13,“N”,14,“O”,15,“P”,16,“Q”,17,“R”,18,“S”,19,“T”,20,“U”,21,“V”,22,“W”,23,“X”,24,“Y”,25,“Z”,26,“a”,27,“b”,28,“c”,29,“d”,30,“e”,31,“f”,32,“g”,33,“h”,34,“i”,35,“j”,36,“k”,37,“l”,38,“m”,39,“n”,40,“o”,41,“p”,42,“q”,43,“r”,44,“s”,45,“t”,46,“u”,47,“v”,48,“w”,49,“x”,50,“y”,51,“z”,52,“0”,53,“1”,54,“2”,55,“3”,56,“4”,57,“5”,58,“6”,59,“7”,60,“8”,61,“9”,62,“-”,63,“_”,64,“=”))))))

[ThinhDinh]

Hi @Krivo and @Robert_Petitto, the function in that script provides native arrayformula so you don’t have to use a long arrayformula in the Sheet.

Paste that into a Script and save it.

Then you can easily call it just like below.

Thinh Dinh's lab V2.0

[Robert_Petitto]

@ThinhDinh this doesn’t copy down for me:

=base64Encode("{"&char(34)&
"property_name"&char(34)&":"&char(34)&A2:A&char(34)&","&char(34)&
"property_address"&char(34)&":"&char(34)&B2:B&char(34)&","&char(34)&
"propertyid"&char(34)&":"&char(34)&C2:C&char(34)&"
}")

Had to use a helper column

[ThinhDinh]

I guess that’s because you wrap text from A, B & C inside the formula, that’s an extra layer so it doesn’t work. Guess the only workaround here is to combine the text first then base64encode it in another column.

[Darren_Murphy]

@Robert_Petitto @ThinhDinh personally I’m not a fan of custom formulas, especially when used with an arrayformula. I’ve found in the past that these can really start to bog things down. The problem is that every time there is a change in that column, all rows will be recalculated and the custom formula is being called multiple times simultaneously. I’ve seen this situation get so bad that you start getting timeouts and the dreaded “#NA” results.

I think a much better approach would be to take the original function that @Krivo supplied…

@Krivo

function Encode64ForMe (url) {

      var encodedVar = Utilities.base64EncodeWebSafe(url,Utilities.Charset.UTF_8);

      return encodedVar;

    }

…and combine that with an onChange() trigger. Then it only gets called when it is needed, and you avoid all the phaffing around with helper columns and arrayformulas, etc.

I did this recently for @Wiz.Wazeer - converted a custom function to an onChange() trigger - and I think he should attest to the fact that it works much better now.

My two cents

[Wiz.Wazeer]

Yes, I still remember that dark hour in the middle of the pandemic when I desperately needed help with a custom function that had been running riot on a script of mine.

At the time, I didn’t know what the source of the cause was until @Darren_Murphy took over.

UnFunnily enough, it was a split function that was splitting my script too (don’t ask, how? I don’t want anyone finding out how dumb I am )!

@Darren_Murphy got rid of the custom function on the sheet, and fixed the script.

The one thing I knew, but now I know I thought I knew, was that the formula was integral to the script, and it worked for a good couple of months until I noticed occurrences of strange behaviour on the sheet.

Very happy with the outcome.