Insert image into Google Sheets cell using Google Sheets API

13
votes

In google apps Script you can insert an image into Google Spreadsheets using the insertImage function (https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertimageblob-column-row).

But I'm not using appscript. I'm using the Google Sheets API (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets) and I can't seem to find a way to do this. Is there any possible implementation?

3
javascriptgoogle-sheetsgoogle-sheets-api

3 Answers

10
votes

The V4 API doesn't have the ability to insert an image blob like Apps Script does (where the image is an overlay on the sheet, not associated with any cell). You may be able to workaround this using the =IMAGE function. We know of the gap in functionality and are looking into adding support for image blobs.

7
votes

Set the formula with script like this:

function showImage() {
 var ss=SpreadsheetApp.getActiveSpreadsheet() 
 var formulaSheet = ss.getSheetByName("Sheet1");
 var formulaCell = formulaSheet.getRange("B5");
 formulaCell.setFormula('=IMAGE("http://finviz.com/fut_chart.ashx?t=ES&p&p=m5&s=m",4,100,200)')
}
5
votes

In the interim, this Google Apps Script Web App will do it (I recommend hosting your own rather than using this one, for privacy and security).

This works with either JSON or application/x-www-form-urlencoded, and whether the URL passed is a link or an actual base64 encoded image url like you might get from https://www.base64-image.de/. 

function doGet(e) {
  return ContentService.createTextOutput("Authorization: Bearer " + ScriptApp.getOAuthToken())
}

//
// Example curl command to insert an image:
// 
// curl -L -d '{ "spreadsheetid": "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E","sheetname": "Sheet1", "imageurl": "https://www.google.com/images/srpr/logo3w.png", "column": 1, "row": 1 }' \
// -H "Authorization: Bearer <INSERT TOKEN RETURNED FROM GET HERE>" \
// -H 'Content-Type: application/json' \
// https://script.google.com/a/tillerhq.com/macros/s/AKfycbzjFgIrgCfZTvOHImuX54G90VuAgmyfz2cmaKjrsNFrTzcLpNk0/exec
//

var REQUIRED_PARAMS = [
  'spreadsheetid', // example: "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E"
  'sheetname',     // Case-sensitive; example: "Sheet1"
  'imageurl',      // Can be an url such as "https://www.google.com/images/srpr/logo3w.png"
                   // or alternately "data:image/png;base64,iVBOR...<snip>...gg=="
  'column', // 1-based (i.e. top left corner is column 1)
  'row'     // 1-based (i.e. top left corner is row 1)
];

function doPost(e) {

  var result = {
    status: "ok",
    defaultMessage: "Image inserted."
  }

  try {
    var params = (e.postData && e.postData.type == "application/x-www-form-urlencoded") ? e.parameter
    : (e.postData && e.postData.type == "application/json") ? JSON.parse(e.postData.contents)
    : undefined;


    if (!params) throw new Error('Unsupported content-type, must be either application/x-www-form-urlencoded or application/json.');

    REQUIRED_PARAMS.forEach(function(requiredParam) {
      if (!params[requiredParam]) throw new Error('Missing required parameter ' + requiredParam);
    });

    SpreadsheetApp.openById(params.spreadsheetid).getSheetByName(params.sheetname).insertImage(params.imageurl, params.column, params.row);  

  } catch(e) {

    console.error(e); 

    result.status = "error";
    result.error = e;
    result.defaultMessage = e.message;

  }  

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON)  
}

Two puzzling things I never figured out:

It worked great all along from within Postman (presumably authed with a cookie) after visiting the URL for the web app and accepting permissions. Unfortunately, I wasn't able to get it working from curl with the Oauth token returned in ScriptApp.getOAuthToken() until I manually added https://www.googleapis.com/auth/drive in the manifest -- which is still a bit of a head scratcher for me.

Here's my resulting manifest:

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
  },
  "webapp": {
    "access": "ANYONE",
    "executeAs": "USER_ACCESSING"
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
}

I was also never able to get it to work converting to a Blob and passing that in to insertImage(), but the URL flavor of insertImage works great with full Base 64 encoded image URLs, so that's a bit annoying but this seems reasonably workable until the Sheets API gets the functionality.

The script (source) itself is shared read-only with the world here:

https://script.google.com/d/1JvFwemL45x3orxFiJf_Gye-JWXaFlzA_MysJsQx06LsH8M2psa9i1H99/edit?usp=sharing

And it's also publicly deployed here, wo if you want to test it out without deploying your own, have at it:

https://script.google.com/a/tillerhq.com/macros/s/AKfycbzjFgIrgCfZTvOHImuX54G90VuAgmyfz2cmaKjrsNFrTzcLpNk0/exec

  • Tim