AppsScript - Replicate "put image in selected cell" on Google Sheets

2
votes

I am currently inserting multiple images in a Google Sheets via AppsScript function InsertImage

With it the function inserts the image but above the cell (not inside the cell). I can insert that image into the cell by clicking the three dots button and then selecting "put image in selected cell" like in this link.

The question here is how to replicate this "put image in selected cell" behaviour via AppsScript (or other coding/scripting methods) so I can automate this action.

It is not an option for me to use the formula IMAGE as that formula doesn't allow me to see the inserted images when using iOS sheets apps...

1
google-apps-scriptgoogle-sheets
Unfortunately, I don't see this functionality in Apps Script nor the Sheets API :/ – Diego
as that formula doesn't allow me to see the inserted images when using iOS sheets apps... ??? – TheMaster
With that comment I mean that once I use IMAGE(url) formula to insert a image from an existing URL, then that image is not shown in the SHEETS app in iOS (for example) (support.google.com/docs/thread/26596783?hl=en) – byle.05
@byle.05 Does my workaround work in sheets app? – TheMaster

1 Answers

4
votes

IssueTracker:

There is currently no method in apps script to insert a image in cell. There is a open feature request here. Consider adding a star(on top left) to it for Google to prioritize the issue.

Workaround:

Here we use =IMAGE() formula to insert a image and copy paste values only to get the raw image. This is mentioned by me here and in comment#65 of the issuetracker.

Sample script:

const insertInCellImage_ = (url, rg) => {
  rg.setValue(`=IMAGE("${url}")`);
  SpreadsheetApp.flush();
  rg.copyTo(rg, { contentsOnly: true });
};

/*Inserts Google logo in Sheet1!A1*/
const test1 = () =>
  insertInCellImage_(
    'https://www.google.com/images/srpr/logo3w.png',
    SpreadsheetApp.getActive()
      .getSheetByName('Sheet1')
      .getRange(1, 1)
  );