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
}

Tanaike · Accepted Answer · 2020-03-02 22:16:02Z

7

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:

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

edited Mar 2, 2020 at 22:16

answered Mar 2, 2020 at 22:00

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alan Wells Over a year ago

Thank you. It works. Interestingly, if the blob name sample is removed, it gives an error. So, for some reason the insertImage method seems to be expecting that the blob has a name.

Tanaike Over a year ago

@Alan Wells Thank you for replying. Yes. Also, I thought that if the image on Spreadsheet could be retrieved by the file name of image, it might help to manage the images on Spreadsheet.

Alan Wells Over a year ago

Good point. If there were multiple images in the Sheet, then having a name for each one would help identify them. I also found another interesting solution. I can use the data url:

var data = 'base64Encoded data here';var url = 'data:image/png;base64,' + data;sh = ss.getSheetByName(po.shName);var image = sh.insertImage(url, column, row);

But that solution doesn't give the image a name.

Tanaike Over a year ago

@Alan Wells Thank you for replying and adding the information. I think that it is a good approach. I had thought that the URL can use only the direct link of the image file. But from your additional information, it was found that the url of data image can be also used. In this case, the base64 data is encoded at the internal server. So I thought that the cost might be lower than that of the process for encoding on the client side.

Alan Wells Over a year ago

What I'm doing is hard coding the base64 data image data in order to avoid an external request to an image url or reading Google Drive to get an image file. I'm trying to create an add-on with as few permissions needed as possible. What would be really great is if there were a way to get the base64 data from html. var html = "<button>Submit</button>"; Obviously the html for the button is a lot less hard coded data than the base64 data.

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Modified script:

References:

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

References:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related