Encode text or substitute

Care to share print screen of the settings?

Here is a simple example:

Results in:

The one I tested with earlier used multiple image overlays and several lines of text, but I can’t share that one - you’ll just have to take my word that it worked :slight_smile:

1 Like

Yeah. Same here. The URL column will encode spaces along with other “unsuitable” characters so that the resulting URL is “compliant”.

Thanks will try again, maybe something with the Hebrew not working well or maybe it’s the comma I have in the text which breaks the link if cloudinary

I think you’re right. Including commas in the text breaks it - even with English text.
I just did some more testing and it appears that the URL column only encodes the parameters - not the path part of the URL construct. I think the fact that it was working for me last night was probably just a fluke. So maybe it’s back to my earlier idea…

The idea I had was to include your entire Cloudinary URL as the parameter part of the URL column (with dummy values for the protocol/host/path), and then use a template column to strip those (and the parameter name) out, leaving just the encoded URL. But I’m not really sure if that will work - I’ll try and find some time to do some testing with it today.

This is what I tried doing but when putting something in the parameters it adds a question mark or equal sign to the link which is not working with cloudinary link

@yinon_raviv take a look at this:

I was able to get it working by using the second method (replacing commas with %E2%80%9A), and then using the result as a replacement in a normal template column (no need for the URL column).

Give that a try and see if it works for you.

Update: Here is a template that double-escapes the standard set of “reserved” characters that are used in a Cloudinary URL…

Using that template, you can take a string of text that looks like this: the, quick: brown/ fox_
And turn it into this: the%252C quick%253A%250A brown%252F%250A fox_%250A
And then that works in a Cloudinary URL

3 Likes

Already got to this point before starting this post…
As I said my problem is that the text is dynamic and every user can enter a different text so a template can’t work for me as I don’t know in advance where the commas will be as well as I can’t do for the spaces and as I’m using fetch for the images and not uploading to cloudinary my image must be encoded as well :man_facepalming:t2:.

On the sheet side I just encoded the image and used substitute function to change any comma in the text dynamically with what you just sent and then encoding the whole text to create a url that is compliant

It doesn’t really matter that the text is dynamic - just run it through a template similar to the one I posted just now :arrow_up: and the commas will be encoded wherever they appear.

2 Likes

This looks promising :star_struck:. Will test asap.
Now just need to figure how to encode the image itself as I use fetch

@yinon_raviv from curiosity - are you fetching glide images or 3rd party images?

Glide images, why are you asking?

@yinon_raviv I did a sample app recently where I auto-uploaded the images to Cloudinary - and thereafter deleted the images from Glide keeping the storage low in Glide. Kind of prefer that to fetch as fetch images are put in the root of the cloudinary account.

There are some differences between fetch and auto-upload - maybe you already know those.

2 Likes

As I’m sure you’re aware, you need to Base64 encode images when using fetch.
If you can find a way to do that in Glide I’ll be super-impressed, but it’s trivial with Apps Script.

Here’s a short snippet that I use for Base64 encoding:

  var file = DriveApp.getFileById(file_id);
  var blob = file.getBlob();
  var file_string = Utilities.base64Encode(blob.getBytes());

You just need to give it the GDrive FileID, which is easy enough to get.

This is a nice idea. Will get deeper to understand the process.
Thank you for sharing

Thanks, yes, I’m aware and actually have a different script for the base64 which is just if you use overlay image, in case if one image, regular encoding suffice.

I did not find a way to do this is glide and 8 think this is to complex to convert to glide.

I did manage to regular encode an image for the fetch for 1 image only with replacing in glide template all the slashes (/) with a %2F which works perfectly.

1 Like

Hi,

It seems my base64 encoding script has the tendency to break and stop working occasionally…
Does your script stable?
If so, will be happy to implement it instead of mine with a little guidance from you

What sort of errors does yours throw when it breaks?
Might not be too difficult to debug and make it a bit more stable.
Can you show me the code?

It’s actually a script that creats a function to be used within the sheet.
from time to time it needs to be deleted and paste again to restart/

here;s the code:

**
 * Base64 Encode Input
 * @param {any | Array<any[]>} input - Input cell, or range of cells
 * @param {boolean} [OPT_webSafe=true] - If should use websafe variant of base64
 * @param {boolean} [OPT_plainText=false] - If should treat input as plaintext instead of UTF-8
 */
function base64Encode(input, OPT_webSafe, OPT_plainText) {
  if (!input) return input;
  const charSet = OPT_plainText ? Utilities.Charset.US_ASCII : Utilities.Charset.UTF_8;
  const useWebSafe = OPT_webSafe !== false;
  const encoder = useWebSafe ? Utilities.base64EncodeWebSafe : Utilities.base64Encode;
  if (Array.isArray(input)) {
    return input.map(t => base64Encode(t, OPT_webSafe, OPT_plainText));
  }

  return encoder(input, charSet);
}

/**
 * Base64 Decode Input
 * @param {any | Array<any[]>} input - Input cell, or range of cells
 * @param {boolean} [OPT_webSafe=true] - If should use websafe variant of base64
 * @param {boolean} [OPT_plainText=false] - If should treat input as plaintext instead of UTF-8
 */
function base64Decode(input, OPT_webSafe, OPT_plainText) {
  if (!input) return input;
  const charSet = OPT_plainText ? Utilities.Charset.US_ASCII : Utilities.Charset.UTF_8;
  const useWebSafe = OPT_webSafe !== false;
  const decoder = useWebSafe ? Utilities.base64DecodeWebSafe : Utilities.base64Decode;
  if (Array.isArray(input)) {
    return input.map(t => base64Decode(t, OPT_webSafe, OPT_plainText));
  }

  return Utilities.newBlob(decoder(input, charSet)).getDataAsString();
}

would love to have your thoughts and even jump on quick call.

When you say it needs to be deleted and pasted again, I assume you’re referring to the values in the spreadsheet, yes?

This is a fairly common problem with custom functions, and for that reason I’m not a big fan.

I’d recommend using a trigger instead. Without seeing what your sheet looks like, I’d probably do something like this:

  • Add another column to the sheet to hold a timestamp - “Encoded At”
  • Each time the trigger runs, check every row that holds the text to be encoded
  • If the corresponding timestamp column is empty, then encode the text in that row and write the current time to the timestamp column

The thing about this approach is if it fails once for any row (due to a transient sheets error), then the timestamp won’t be written and it should then pick it up on the next run.

I can be available for a call, if you’d like to talk it through, just PM me.

1 Like