Convert Base64 String to image

RenatoE
Participant III

I have a Google spreadsheet where a column contains the string that corresponds to an image in Base64. For example: /9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYโ€ฆ
How can I convert this back to an image link so that App sheet can display the image?

Solved Solved
1 16 6,177
1 ACCEPTED SOLUTION

Great, this information is helping a lot.
I got it working and now I need to work on the trigger function.

The working script is below:

function myFunction() {
var imgFolder = DriveApp.getFolderById(โ€˜IMAGE FOLDER URLโ€™); //id string from URL of image folder
var sheetName = โ€˜NAME OF SHEETโ€™; //name of sheet to write results
var imgCol = 12 // column containing image path/filename for my case in particular
var imgStringCol = 11 // column with Base64 string for my case in particular
var dateStringCol = 9 //column with date and time for my case in particular

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();

if (sheet.getRange(lastRow, imgCol).getValue()==""){ // THIS WILL CHECK IF THE IMAGE ALREADY HAS A LINK OR NOT

var filename = sheet.getRange(lastRow, dateStringCol).getValue(); //I AM MAKING THE IMAGE NAME EQUAL TO THE DATE AND TIME TAKEN
var filenamereplace = filename.replace(":", "-"); // I AM REMOVING SEMICOLON FROM THE FILE NAME OTHERWISE APPSHEET WILL NOT RECOGNIZE THE FILE
var filenamereplace2 = filenamereplace.replace(":", "-"); // I AM REMOVING A SENCOND SEMICOLON IN MY PARTICULAR CASE
var filenamereplace3 = filenamereplace2 + ".jpg"; // ADDING THE CORRECT FILE FORMAT TO THE NAME
var base64 = sheet.getRange(lastRow, imgStringCol).getValue();

try{
  var decoded = Utilities.base64Decode(base64);
  var blob = Utilities.newBlob(decoded, MimeType.JPEG, filenamereplace3);
  imgFolder.createFile(blob);
  var imgPath = "Placas/" + filenamereplace3 // PLACAS IS THE FOLDER NAME WHERE I AM STORING THE IMAGES
  sheet.getRange(lastRow, imgCol).setValue(imgPath); // PUTTING THE FILENAME IN THE APPROPIATE COLUMN
}catch(err){Logger.log(err)}
}

Logger.log(โ€œendโ€);
}

View solution in original post

16 REPLIES 16

Steve
Participant V

AppSheet does not have any inherent method to convert this; base64-encoded images are not supported.

GreenFlux
Participant V

I wrote a script to go the other direction (image file to B64 string). Are you familiar with Apps Script? It wouldnโ€™t take much to modify this to go from string to image.
https://community.appsheet.com/t/base64decode-images-to-string-with-apps-script-for-use-in-webhook-t...

Yes I read your script but since I have never used Apps Script I was wondering if there was an easier way.

From what I have read so far the process would go something like this:

  1. The sheet would detect that a new row has been added.
  2. The Apps Script would triger a conversion using your script in reverse.
  3. The script would save the image somewhere in Google Drive
  4. A link would be created on the google sheet to the image in the drive.
  5. App sheets would then recognize the imag.

That is correct. It would be asynchronous though.

So it may not run fast enough for the sheet to see the updated link to your image after the sync that saves your B64 string. It may have to sync a second time before the image URL is detected.

Later today or tomorrow I will attempt to re-write the script. I hope that you can help me since I dont have any experience with these scripts.

Sure, I can take a look if you get stuck on something.

Ok, here is my take on what is needed.
I obtained the required conversion code from here:
//https://stackoverflow.com/questions/35236232/create-image-file-base64-to-blob-using-google-appscript

My comments where I think I need help are in UPPERCASE:

var imgFolder = DriveApp.getFolderById(โ€˜FOLDER_IDโ€™); //id string from URL of image folder
var sheetName = โ€˜PlacasALPRโ€™; //name of sheet to write results
var imgCol = L // column containing image path/filename
var resultCol = K // column with Base64 string

function Base64Toimg(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var lastRow = sheet.getLastRow();
if (sheet.getRange(lastRow, imgCol).getValue()==""){// I THINK THIS WILL CHECK IF THE IMAGE ALREADY HAS A LINK OR NOT

var filename = sheet.getRange(lastRow, I).toString(); //I WANT TO MAKE IMAGE NAME EQUAL TO THE DATE AND TIME TAKEN SO I NEED TO CONVERT THE "I" COLUMN TO A STRING
var base64 = sheet.getRange(lastRow, resultCol)
Logger.log(filename);
try{
  var id = LatestFile();
  var decoded = Utilities.base64Decode(base64);
  var blob = Utilities.newBlob(decoded, MimeType.JPEG, "filename");
  currentFolder.createFile(blob);
}catch(err){Logger.log(err)}
finally{    
  sheet.getRange(lastRow, imgCol).xxxxxx(decoded); // NEED A FUNCTION TO CREATE DE FILE LINK
  return data; // NOT SHURE WHAT THIS WILL DO
}

}
}
// I DONT UNDERSTAND IF ANY OF THIS IS NEEDED
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
}

OOPS, not sure how to keep all in the code box.

No, you wonโ€™t need this function. I was using this to find the image file as an input to the script. Your input will be a string, which you can pull directly from the sheet.

You may want to rename the variables to apply to your use-case.
var resultCol would make more sense as your image path. In my case it was the string, but you want to return an image.

Thanks I have changed the resultCol to โ€œimgStringColโ€

I still have some modified code that I am not sure if it will work. Maybe you can comment on the following lines when you have some time:

.
.
if (sheet.getRange(lastRow, imgCol).getValue()==""){// I THINK THIS WILL CHECK IF THE IMAGE ALREADY HAS A LINK OR NOT
.
.
var filename = sheet.getRange(lastRow, I).toString(); //I WANT TO MAKE IMAGE NAME EQUAL TO THE DATE AND TIME TAKEN SO I NEED TO CONVERT THE โ€œIโ€ COLUMN TO A STRING
.
.
sheet.getRange(lastRow, imgCol).xxxxxx(decoded); // NEED A FUNCTION TO CREATE DE FILE LINK
return data; // NOT SHURE WHAT THIS WILL DO
.
.

Here is the new complete code:

function myFunction() {
var imgFolder = DriveApp.getFolderById(โ€˜1bCC68ecE6SAUR91hsabcHkRZONxqjQodโ€™); //id string from URL of image folder
var sheetName = โ€˜Copy of PlacasALPRโ€™; //name of sheet to write results
var imgCol = 12 // column containing image path/filename
var imgStringCol = 11 // column with Base64 string
var dateStringCol = 9 //column with date and time

function Base64Toimg(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var lastRow = sheet.getLastRow();
if (sheet.getRange(lastRow, imgCol).getValue()==""){// I THINK THIS WILL CHECK IF THE IMAGE ALREADY HAS A LINK OR NOT

var filename = sheet.getRange(lastRow, dateStringCol).toString(); //I WANT TO MAKE IMAGE NAME EQUAL TO THE DATE AND TIME TAKEN SO I NEED TO CONVERT THE "I" COLUMN TO A STRING
var base64 = sheet.getRange(lastRow, imgStringCol)
Logger.log(filename);
try{
  var id = LatestFile();
  var decoded = Utilities.base64Decode(base64);
  var blob = Utilities.newBlob(decoded, MimeType.JPEG, "filename");
  currentFolder.createFile(blob);
}catch(err){Logger.log(err)}
finally{    
  sheet.getRange(lastRow, imgCol).xxxxxx(decoded); // NEED A FUNCTION TO CREATE DE FILE LINK
  return data; // NOT SHURE WHAT THIS WILL DO
}

}
}
}

It looks like youโ€™re on the right track.

Is this for displaying in the app, or as an external link that will work outside of AppSheet?

AppSheet image columns store a relative_path/file_name in the spreadsheet (not a fully qualified URL), but you can create public links to those images.

Since youโ€™re creating a file from a string in your case, you know the filename and can save that to the sheet using
sheetName.getRange(lastRow, imgCol).setValue(filename).

Just save it in the right format with whatever folder prefix is currently being used when you upload an image to that same column through the app.

If you need a URL that works outside of AppSheet, see this post:
https://community.appsheet.com/t/convert-an-image-to-xy/14190/3?u=greenflux

Also, you can remove var id, as that was part of the LatestFile() function I used to find the image to convert. Youโ€™re starting with a string in your sheet, so the whole part about finding the latest file is unneeded.

Great, this information is helping a lot.
I got it working and now I need to work on the trigger function.

The working script is below:

function myFunction() {
var imgFolder = DriveApp.getFolderById(โ€˜IMAGE FOLDER URLโ€™); //id string from URL of image folder
var sheetName = โ€˜NAME OF SHEETโ€™; //name of sheet to write results
var imgCol = 12 // column containing image path/filename for my case in particular
var imgStringCol = 11 // column with Base64 string for my case in particular
var dateStringCol = 9 //column with date and time for my case in particular

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();

if (sheet.getRange(lastRow, imgCol).getValue()==""){ // THIS WILL CHECK IF THE IMAGE ALREADY HAS A LINK OR NOT

var filename = sheet.getRange(lastRow, dateStringCol).getValue(); //I AM MAKING THE IMAGE NAME EQUAL TO THE DATE AND TIME TAKEN
var filenamereplace = filename.replace(":", "-"); // I AM REMOVING SEMICOLON FROM THE FILE NAME OTHERWISE APPSHEET WILL NOT RECOGNIZE THE FILE
var filenamereplace2 = filenamereplace.replace(":", "-"); // I AM REMOVING A SENCOND SEMICOLON IN MY PARTICULAR CASE
var filenamereplace3 = filenamereplace2 + ".jpg"; // ADDING THE CORRECT FILE FORMAT TO THE NAME
var base64 = sheet.getRange(lastRow, imgStringCol).getValue();

try{
  var decoded = Utilities.base64Decode(base64);
  var blob = Utilities.newBlob(decoded, MimeType.JPEG, filenamereplace3);
  imgFolder.createFile(blob);
  var imgPath = "Placas/" + filenamereplace3 // PLACAS IS THE FOLDER NAME WHERE I AM STORING THE IMAGES
  sheet.getRange(lastRow, imgCol).setValue(imgPath); // PUTTING THE FILENAME IN THE APPROPIATE COLUMN
}catch(err){Logger.log(err)}
}

Logger.log(โ€œendโ€);
}

RenatoE
Participant III

Also how can I get this function in my spreadsheet?

From the Spreadsheet Menu, Tools/Script Editor,

Then from the Script Menu, Edit/Current Projectโ€™s Triggers

Then click Add Trigger on the bottom right.

Excellent, the trigger is now working!
And thank you very much for the help!!!

Glad you got it working!

Possible solution inside AppSheet:

Top Labels in this Space