JS Adding/Removing image from cell (toggle button)-CodePudding

I am looking for a way to add and remove an image in a cell.

I have the button assigned to this function but I can't seem to add it to the dynamic range. If I hardcode the column and row for testing it adds the image fine but then I can't figure out how to delete it.

function test() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var ranges = selection.getActiveRangeList().getRanges();
  for (var i in ranges) {
    var data = ranges[i].getValues();
    for (var row in data) for (var col in data[row]) {
        var cell = data[row][col];
        if (cell == '') continue;                      // if empty --> go to next cell
        IF CELL CONTAINS IMAGE REMOVE IT
          continue;                                    // --> go to next cell
        } else {
          sheet.insertImage("URL", [col], [row], 125, 2); //Add image at col/row
        }
    }
    ranges[i].setValues(data);
  }
}

Before button click

After button click

Select new cells

After button click

CodePudding user response：

I believe your goal is as follows.

When the script is run after the cells are selected, you want to put the image on the cell.
When the image is not put on the cell, you want to put the image on the cell.
When the image has already been put on the cell, you want to remove the image.
When the cell value is empty, you don't want to do anything.

In this case, how about the following modified script?

Modified script:

function test() {
  const url = "###"; // Please set your URL.

  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getActiveRangeList().getRanges();
  let images = sheet.getImages();
  ranges.forEach(r => {
    const row = r.getRow();
    const col = r.getColumn();
    const numRows = r.getNumRows();
    const numCols = r.getNumColumns();
    for (let i = 0; i < numRows; i  ) {
      for (let j = 0; j < numCols; j  ) {
        if (sheet.getRange(row   i, col   j).isBlank()) continue;
        const image = images.filter(e => {
          const anchor = e.getAnchorCell();
          return anchor.getRow() == row   i && anchor.getColumn() == col   j;
        });
        if (image.length > 0) {
          image.forEach(e => e.remove());
          images = sheet.getImages();
        } else {
          sheet.insertImage(url, col   j, row   i, 125, 2);
        }
      }
    }
  });
}

At first, please set your URL.
When you use this script, please select the cells and run the script.
When you run this script for the selected cells, the images are put on the cells which have no images, and the images are removed from the cells which have the images.
In this case, the image on the cells can be checked using the method of getAnchorCell() of Class OverGridImage.

Modified script:

References: