How to insert an image directly into a Google Spreadsheets cell?

Question

Google Apps Script

Good afternoon. Recently, the function of generating a QR code in Google sheets was needed. Did it like this:

function getQR(text, size){
  size = (size || 200) + "";
  const apiURL = "https://api.qrserver.com/v1/create-qr-code/?size="+size+"x"+size+"&data="+text;
  let result = UrlFetchApp.fetch(apiURL);
  return result.getBlob().getAs("image/png");
};

but the picture does not appear in the cell, although the url is built valid and the server returns the png picture.
Of course, I later did it through IMAGE (), but still the question stuck in my head, because periodically need to do this. LaTeX formulas or your diagram, for example.

Answer 1

The process of inserting a picture is quite specific and may require certain skills. Therefore, I will give a ready-made example based on public code. All this does not work very fast, so you need to think about pre-prepared pictures and where you will insert them. Portions of the function are as follows: on one sheet, an array of pictures with coordinates. Those. You can add multiple images to one sheet at once. This will be the most optimal way precisely because of the specifics of the insert.
Функция вставки

/**
 * Insert a single image to the cell. A1 is default
 *
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
 * @param {GoogleAppsScript.Base.Blob} blob
 * @param {number} row
 * @param {number} column
 */
function insertImageBlobToCell_(sheet, blob, row = 1, column = 1) {
  const sheetName = sheet.getName();
  const parentId = sheet.getParent().getId();
  return DocsServiceApp.openBySpreadsheetId(parentId)
    .getSheetByName(sheetName)
    .insertImage([{ blob, range: { row, column } }]);
}

Пример вызова

/**
 * Insert an image blob to the cell
 */
function userActionRun() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const blob = UrlFetchApp.fetch(
    'https://contributor.pw/img/post/sheets/sheets_mmult-some-uses-cases_01.png'
  ).getBlob();
  insertImageBlobToCell_(sheet, blob);
}

Результат

Don't forget to add the library

108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW

to your project. Sample manifest in a snippet