Insert Image into a Google Sheet with Apps Script using base64 encoded data

Question

Given some base64 encoded data for a png file, as in the example below from the image tag.

<img src="data:image/png;base64,R0lGODlhDAAMAKIFAF5LAP/zxAAAANyuAP/gaP///wAAAAAAACH5BAEAAAUALAAAAAAMAAwAAAMlWLPcGjDKFYi9lxKBOaGcF35DhWHamZUW0K4mAbiwWtuf0uxFAgA7">

I need to create an image blob from the base64 encoded data for the insertImage() method.

sheetClass.insertImage(imageBlob, column, row)

Documentation: Sheets Class - insertImage method

I've tried using the code below, but it throws the error:

Execution failed: Error retrieving image from URL or bad URL

The documentation states that the method needs a blob, not a URL, but it seems like it's expecting a link to an image file.

function insertImageFromBase64Src() {
  var data = 'R0lGODlhDAAMAKIFAF5LAP/zxAAAANyuAP/gaP///wAAAAAAACH5BAEAAAUALAAAAAAMAAwAAAMlWLPcGjDKFYi9lxKBOaGcF35DhWHamZUW0K4mAbiwWtuf0uxFAgA7';

  var imageBlob = Utilities.newBlob(Utilities.base64Decode(data), 'image/png').getBytes();

  var ss = SpreadsheetApp.getActiveSpreadsheet();//This code is bound to a Sheet

  var po = {
    shName:'Update File',
    column:1,
    row:37
  }

  var sh = ss.getSheetByName(po.shName);

  var image = sh.insertImage(imageBlob, po.column, po.row);//Insert an image and return the image
}

Answer 1

How about this modification?

Modification points:

• insertImage can use the blob and URL which is the direct link of the image file.

  • In your script, imageBlob is an byte array which is "number[]". By this, I think that an error occurs.

• Please add the name to the blob.

  • When the name is not given, an error occurs.

Modified script:

When your script is modified, it becomes as follows.

function insertImageFromBase64Src2() {
  var data = 'R0lGODlhDAAMAKIFAF5LAP/zxAAAANyuAP/gaP///wAAAAAAACH5BAEAAAUALAAAAAAMAAwAAAMlWLPcGjDKFYi9lxKBOaGcF35DhWHamZUW0K4mAbiwWtuf0uxFAgA7';

  var imageBlob = Utilities.newBlob(Utilities.base64Decode(data), 'image/png', 'sample');  // Modified

  var ss = SpreadsheetApp.getActiveSpreadsheet();  //This code is bound to a Sheet

  var po = {
    shName:'Update File',
    column:1,
    row:37
  }

  var sh = ss.getSheetByName(po.shName);

  var image = sh.insertImage(imageBlob, po.column, po.row);//Insert an image and return the image
}

• In this modification, it supposes that data can be correctly decoded.

References:

• insertImage(blobSource, column, row)
• insertImage(url, column, row)

If this was not the direct solution of your issue, I apologize.