AppScript to fix Google Sheets with =IMAGE() formulas that contain data-urls

example.tsv

Image key	Image	Another column
1	=IMAGE("data:image/gif;base64,R0lGODlhEAAQAMQAAORHHOVSKudfOulrSOp3WOyDZu6QdvCchPGolfO0o/XBs/fNwfjZ0frl3/zy7////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAkAABAALAAAAAAQABAAAAVVICSOZGlCQAosJ6mu7fiyZeKqNKToQGDsM8hBADgUXoGAiqhSvp5QAnQKGIgUhwFUYLCVDFCrKUE1lBavAViFIDlTImbKC5Gm2hB0SlBCBMQiB0UjIQA7")	Random data
2	=IMAGE("data:image/gif;base64,R0lGODlhEAAQAMQAAORHHOVSKudfOulrSOp3WOyDZu6QdvCchPGolfO0o/XBs/fNwfjZ0frl3/zy7////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAkAABAALAAAAAAQABAAAAVVICSOZGlCQAosJ6mu7fiyZeKqNKToQGDsM8hBADgUXoGAiqhSvp5QAnQKGIgUhwFUYLCVDFCrKUE1lBavAViFIDlTImbKC5Gm2hB0SlBCBMQiB0UjIQA7")	More Random data

image-replacer.js

// This App Script fixes the missing Google Sheets API that lets you add images as data-uris.
// Surprisingly, neither the =IMAGE() formula or the public web API accepts base64 encoded 
// data-uris as an image URL. 
//
// However, the App Script API does support inserting image data-urls! So we use this as
// a workaround until Google adds API support.
//
// HOW TO USE:
// 1. Create a CSV or spreadsheet
// 2. Convert your image to base64 and add it to a cell, optionally as the url parameter to the IMAGE formula:
//    `=IMAGE("data:image/gif;base64,R0lGODlhEAAQAM...")`
//    (Note: the max length of contents of a cell is 50,000 characters, so make sure to compress your images accordingly.
// 3. Add this script as an AppScript
// 4. Select the column that contains the images
// 5. Run the script
// 6. It will convert all the data uris in that entire column and insert them as cell images

const imageDimensions = {
  width: 256,
  height: 256
}

function insertImagesFromBase64() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const activeRange = sheet.getActiveRange();

  // Get the row index of the selected cell
  const columnIndex = activeRange.getColumn();

  // console.log("checking rows " + rowRange.getA1Notation() + ` last: ${sheet.getLastRow()}`);

  let successCount = 0;
  for (let rowIndex = 1; rowIndex <= sheet.getLastRow(); rowIndex++) {
    const range = sheet.getRange(rowIndex, columnIndex); // Offset for 1-indexing
    const cellValue = range.getValue();

    console.log("checking cell " + range.getA1Notation());
    // Check if the cell contains base64 data
    if (cellValue.includes("data:image")) {
      // Extract the base64 data (remove the data URI prefix)
      const start = cellValue.indexOf('data:image');
      let base64Data = cellValue.substring(start);

      // remove ") in case it's wrapped in a useless =IMAGE() formula
      base64Data = base64Data.replace(/[)"]/gi, '');

      // Create a CellImage object
      const cellImage = SpreadsheetApp.newCellImage()
        .setSourceUrl(base64Data) // Reconstruct for CellImage
        .setAltTextTitle(cellValue)
        .build();

      // Insert the image into the cell
      range.setValue(cellImage); 

      // Resize the row and column to fit the image
      sheet.setRowHeight(range.getRow(), imageDimensions.height);
      sheet.setColumnWidth(range.getColumn(), imageDimensions.width);

      successCount++
    }
  }

  if (successCount === 0) {
    SpreadsheetApp.getUi().alert(`Could not find any base64 images in the selected cells ${activeRange.getA1Notation()}`);
  }
}