I am using the =IMAGE(URL) formula in google sheets. The images are loaded from the url and then displayed in the cells. I want to stop using the url to fetch images, and instead "download" them to display them. So if the url changes it remains active.

How can I do that ? :) Thanks!

CodePudding user response：

If there are just a couple of images, copy the URL from each image() formula and use Insert > Image > Image in cell > By URL to replace the formula with the image.

To automate that, you will need a script. Try something like this:

function test() {
  const sheetRegex = /^(Sheet1|Sheet2|Sheet3)$/i;
  let numRangesModified = 0;
  let totalNumReplaced = 0;
  const ss = SpreadsheetApp.getActive();
  let sheets;
  try {
    ss.toast(`Replacing image formulas with in-cell images...`, 'Please wait', 30);
    const sheets = ss.getSheets()
      .filter(sheet => sheet.getName().match(sheetRegex));
    if (!sheets.length) {
      ss.toast(`Cannot find any sheets that match ${sheetRegex.toString()}.`);
      return;
    }
    const ranges = sheets.map(sheet => sheet.getDataRange());
    ranges.forEach(range => {
      const numReplaced = replaceImageFormulasWithImages_(range);
      if (numReplaced) {
        numRangesModified  = 1;
        totalNumReplaced  = numReplaced;
      }
    });
    ss.toast(`Replaced ${totalNumReplaced} image formula(s) with in-cell images in ${numRangesModified} range(s).`, 'Done', 10);
  } catch (error) {
    ss.toast(`Replaced ${totalNumReplaced} image formula(s) in ${numRangesModified} range(s). ${error.message}`, 'Error', 30);
  }
}

/**
* Replaces all image() formulas in a range with in-cell images.
*
* Supports formulas like image(E2) where E2 contains a URL, and
* formulas like image("https://www.example.com/pict/image01.png").
* The replacement is done cell by cell to work around an issue
* in the SpreadsheetApp API.
*
* @param {SpreadsheetApp.Range} range The range where to replace image() formulas with in-cell images.
* @return {Number} The number of image() formulas replaced with in-cell images.
*/
function replaceImageFormulasWithImages_(range) {
  // version 1.1, written by --Hyde, 9 December 2022
  //  - add cellA1
  // version 1.0, written by --Hyde, 8 December 2022
  //  - see https://stackoverflow.com/a/74736461/13045193
  const sheet = range.getSheet();
  let numReplaced = 0;
  range.getFormulas().forEach((row, rowIndex) =>
    row.forEach((formula, columnIndex) => {
      if (formula.match(/^(=image)/i)) {
        let url;
        let match = formula.match(/^=image\("(http[^"] )"/i);
        if (match) {
          url = match[1];
        } else {
          match = formula.match(/^=image\(([A-Z]{1,3}\d{1,6})\)/i);
          if (match) {
            let cellA1 = match[1];
            try {
              url = sheet.getRange(cellA1).getDisplayValue();
            } catch (error) {
              ;
            }
          }
        }
        if (url) {
          range.offset(rowIndex, columnIndex, 1, 1).setValue(
            SpreadsheetApp
              .newCellImage()
              .setSourceUrl(url)
              .build()
          );
          numReplaced  = 1;
        }
      }
    })
  );
  return numReplaced;
}

Replace /^(Sheet1|Sheet2|Sheet3)$/i with a regular expression that matches the sheet names where you want to replace image() formulas with in-cell images. To replace on all sheets, use /./i.