Set Image To Google Sheet Cell

INTRO

In the past, many users of google sheets have set images to cells in their spreadsheet, but then found that it is not "programmatically" possible to get the image into their app, or to set an "image to cell" from their app using google apps script. As of January 2022, this all changed, Google have introduced some functions that allow developers to now do this - with some caveats. (Please note that this is different from using the IMAGE= spreadsheet function to display an image in a cell.)

The Google documentation is HERE and HERE, it is somewhat terse, and there are no examples. I have also found that regardless of the method I use, or the location of the source image file, the functions will not return the image url (.getUrl()). However, it is possible to store information in the AltTextDescription().

I must give credit to "Kos" on Stackoverflow, for their post and examples, which I used to put this guide together.

This guide should demonstrate, with use of a google apps script, how to do the following from an AI2 app:

• Upload an image from AI2 to Google Drive, and set that image to a cell

• Set an image to a cell from an image already in google drive

• Set a generated qrCode as an image in cell (using google's qrcode generator)

• Set an image to cell from a fully qualified external url to an image (e.g. an image on a server somewhere)

• Fetch the information contained in AltTextDescription for an image in cell

In each of the the first four actions, the file ID/Filename/url will be stored in AltTextDescription, and this is returned to the app with the fifth action for further use.

I used my image-base64 extension to get the base64String of the image in the image component, for uploading a file. 

Users can add images directly to cells on the spreadsheet, but they must remember to include the required data in AltTextDescription to be able to return this to the app.

SCRIPT

function doPost(e) {

    var ss = SpreadsheetApp.openById(e.parameters.sheetId);

    var sh = ss.getSheetByName(e.parameters.sheetName);

    var cell = e.parameters.cellRef;

    var data = Utilities.base64Decode(e.parameters.data);

    var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);

    var fileId = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();

    var image = SpreadsheetApp.newCellImage().setSourceUrl('https://drive.google.com/uc?id=' + fileId).setAltTextDescription('[' + e.parameters.filename +", "+ fileId +']').toBuilder().build();

    sh.getRange(cell).setValue(image);

    return ContentService.createTextOutput("Image Set to: " + cell);

    }

function doGet(e) {

  if (e.parameter.fn == 'GET') {

  var ss = SpreadsheetApp.openById(e.parameter.sheetId);

  var sh = ss.getSheetByName(e.parameter.sheetName);

  var value = sh.getRange(e.parameter.cellRef).getValue();

  return ContentService.createTextOutput(value.getAltTextDescription());

  }

  else if (e.parameter.fn == 'SET') {

    var ss = SpreadsheetApp.openById(e.parameter.sheetId);

    var sh = ss.getSheetByName(e.parameter.sheetName);

    var fileId = e.parameter.fileId;

    var cell = e.parameter.cellRef;

    var fname = DriveApp.getFileById(fileId).getName();

    var image = SpreadsheetApp.newCellImage().setSourceUrl('https://drive.google.com/uc?id=' + fileId).setAltTextDescription('[' + fname +", "+ fileId +']').toBuilder().build();

    sh.getRange(cell).setValue(image);

    return ContentService.createTextOutput("Image: " + fname + " Set to: " + cell);

    }

  else if (e.parameter.fn == 'QR') {

    var ss = SpreadsheetApp.openById(e.parameter.sheetId);

    var sh = ss.getSheetByName(e.parameter.sheetName);

    var qrCodeUrl = 'https://chart.googleapis.com/chart?cht=qr&chs=200x200&chld=L%7C2&chl=' + e.parameter.qrCodeValue;

    var cell = e.parameter.cellRef;

    //var fname = DriveApp.getFileById(fileId).getName();

    var image = SpreadsheetApp.newCellImage().setSourceUrl(qrCodeUrl).setAltTextDescription('[' + qrCodeUrl +']').toBuilder().build();

    sh.getRange(cell).setValue(image);

    return ContentService.createTextOutput("qrCodeUrl: " + qrCodeUrl + " Set to: " + cell);

    }

  else if (e.parameter.fn == 'EXT') {

    var ss = SpreadsheetApp.openById(e.parameter.sheetId);

    var sh = ss.getSheetByName(e.parameter.sheetName);

    var fileUrl = e.parameter.fileUrl;

    var cell = e.parameter.cellRef;

    //var fname = DriveApp.getFileById(fileId).getName();

    var image = SpreadsheetApp.newCellImage().setSourceUrl(fileUrl).setAltTextDescription('['+ fileUrl +']').toBuilder().build();

    sh.getRange(cell).setValue(image);

    return ContentService.createTextOutput("Image: " + fileUrl + " Set to: " + cell);

    }

}

BLOCKS