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.

3 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