Base64Decode Images to String with Apps Script, for Use in Webhook Templates

AppSheet webhooks can send POSTs to create records in other web-services but they don’t offer multipart/Form-Data templates. There’s also no expressions for converting the image to a Base64 string, so there’s no easy way to include images in webhooks.

The following Apps Script code can be added to your spreadsheet to monitor for new images and write the Base64 strings back to a new column of that row in the sheet.

Paste code into new script: Tools/Script Editor

var imgFolder = DriveApp.getFolderById('YOUR_SHEET_ID');  //id string from URL of image folder
var sheetName = 'YOUR_SHEET_NAME';  //name of sheet to write results
var imgCol = NUMBER // number of column containing image path/filename
var resultCol = NUMBER // number of column to store Base64 string
var pathString = 'FOLDER_NAME/';  // Path to be stripped from string to get just filename

function imgToBase64(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  if (sheet.getRange(lastRow, resultCol).getValue()==""){
    
    var filename = sheet.getRange(lastRow, resultCol).toString().replace(pathString,"");
    Logger.log(filename);
    try{
      var id = LatestFile();
      var file = DriveApp.getFileById(id); 
      var data = Utilities.base64Encode(file.getBlob().getBytes());
    }catch(err){Logger.log(err)}
    finally{    
      sheet.getRange(lastRow, resultCol).setValue(data);
      return data;
    }
  }
}

function LatestFile() {
  var files = imgFolder.getFiles();
  var result = [];
 do {
    var file = files.next();
    
    result.push([file.getDateCreated(),file.getId()]);
 } while (files.hasNext()) ;
  Logger.log(result);
  result.sort();
  result.reverse();
  var id = result[0][1];
  Logger.log(id);
  return id;// return most recent file ID in imgFolder
}

Update the first 5 lines according to your own sheet Id, name, etc, then

Name and Save the script, and finally,

Set up an onEdit Trigger for the imgToBase64 function

Test by adding a new row with an image, and the resultCol-column will update with the image as a Base64 string.

Screen Shot 2020-02-11 at 1.44.15 PM

That’s it! You now have a plain text string version of your image that’s compatible with AppSheet webhooks!

WARNING!!
This approach relies on the assumption you want the ‘most recent’ image in the folder, so there’s a chance it could grab the wrong ID if multiple users are adding images at the same time.

Feedback Welcome!
I know this method is not perfect. I’m interested to hear your ideas for improvement, or other approaches to the image-webhook limitation.

6 Likes

Using virtual column and expression we create public full path for each image file we uploaded on appsheet. Then we can utilize such a full path for any other usage even use the image file outside Appsheet. I do that in a few of my working app.

2 Likes

Here ist how you can create the direct link to a image file.

@GreenFlux For what do you use the Base64?

1 Like

Thanks @Fabian! I’m using the Baes64 string to upload images to Shopify Products. A public url to the image would work too. But I couldn’t find any documentation on generating the public link so I went this route.

1 Like

I just found out, that Images are stored as Base64.
If you take a photo with form and then open the detail view, right click on the image and open it in another tab. You will get the Base64 of this image.
After the row is synced you will see that the image will disappear for milli seconds and appear again. This time it will have a URL.

1 Like

You can also store images as base64 blobs in sql server, etc.

1 Like

Does anybody know how to get the Base64 image? I mean as mentioned above, it’s already there.

Adding @Bellave_Jayaram as you mentioned this also in the Feature Requests:

I wrote a script for it.

But I don’t know how to do it without Apps Script. We really need expressions for BASE64ENCODE() and BASE64DECODE() to be able to make use of it easily in the app.
__

Are you sending webhooks to create records externally and trying to include an image column?

This is what I’m doing:

Base64 images would solve this problem.
The thing is: When you capture an image, AppSheet already stores this image as Base64.

2 Likes

Yes, it’s too bad we can’t access the Base64 version that’s already being stored by AppSheet.

The Base64 version would also be useful for POST-ing a row with the image included, since AppSheet webhooks lack support for multi-part forms with mixed content-types.

I wonder if you could stores images as base64 blobs in SQL server, and then use a longtext virtual column to access the raw base64 data?

@GreenFlux
Why don’t you create an onChange(e) script for your app which can change the image file’s sharing via setSharing(ACCESS.MODE, PERMISSION.MODE) so that you can create a publicly available URL of your image to use in Shopify.

For my use case, the goal was to upload the image to Shopify, then use the Shopify-host, public image URL and discard the Google Drive version. The Base64 string allowed sending the entire row (column values and images) in a single webhook as all text.