Ignore cells that already contain an image

Question

I have two columns "B" and "C". Column "B" contains urls. Column "C" should contain the image of the url of the row in column "B".

In that case, my code works correctly. However, each time the function is executed, it regenerates the images in the cells that already contained images, therefore the execution of the function takes a long time to fully execute.

Also, some urls have an expiration time, so after a while, if you try to generate the image again, the function returns an error because the URL has expired.

Therefore my intention is that at the beginning of the script there is a conditional to check if the cells of column "C" already contain an image or not.

If it contains an image, ignore that cell and move on to the next one. If it doesn't contain an image, then it executes the function and places the image.

This is my code:

function imagenIcono() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('📅 Todos los eventos');
  const lastRow = sheet.getLastRow();
  
  var rango = sheet.getRange(2, 2, lastRow -1, 2);
  var values = rango.getValues();

  values.forEach((fila)=> {

    // Pon la imagen en todas las celdas donde la url no sea con extensión ".svg"
    if ( !fila[0].toString().includes(".svg")) { 

      fila[1] = SpreadsheetApp.newCellImage().setSourceUrl(fila[0]).toBuilder().build();

    } else {

      Logger.log(fila[0]);
      var blob = UrlFetchApp.fetch(fila[0]).getBlob();
      var id = DriveApp.createFile(blob.setName("temp")).getId();
      Utilities.sleep(2000);
      var { thumbnailLink } = Drive.Files.get(id);
      fila[1] = SpreadsheetApp.newCellImage().setSourceUrl(thumbnailLink.replace("=s220", "=s1000")).toBuilder().build();
      DriveApp.getFileById(id).setTrashed(true);

    }

  }) 

  rango.setValues(values);
}

Answer 1

I believe your goal is as follows.

• You want to skip the cells including an image and put a new image into the empty cell.

Issue and workaround:

In the current stage, it seems that the CellImage retrieved by getValue and getValues cannot be directly used with setValue and setValues. I would like to believe that this issue will be resolved in the future update. From this situation, as a current workaround, how about the following flow?

1. Retrieve all values from cells.
2. Check whether the CellImage is included in the cell.
   • When the cell has an image, no script is run.
   • When the cell has empty, a new image is put into the cell.

When this flow is reflected in your script, how about the following modification?

From:

values.forEach((fila)=> {

  // Pon la imagen en todas las celdas donde la url no sea con extensión ".svg"
  if ( !fila[0].toString().includes(".svg")) { 

    fila[1] = SpreadsheetApp.newCellImage().setSourceUrl(fila[0]).toBuilder().build();

  } else {

    Logger.log(fila[0]);
    var blob = UrlFetchApp.fetch(fila[0]).getBlob();
    var id = DriveApp.createFile(blob.setName("temp")).getId();
    Utilities.sleep(2000);
    var { thumbnailLink } = Drive.Files.get(id);
    fila[1] = SpreadsheetApp.newCellImage().setSourceUrl(thumbnailLink.replace("=s220", "=s1000")).toBuilder().build();
    DriveApp.getFileById(id).setTrashed(true);

  }

}) 

rango.setValues(values);

To:

values.forEach((fila, i) => {
  if (fila[1].valueType == SpreadsheetApp.ValueType.IMAGE) return;
  if (!fila[0].toString().includes(".svg")) {
    var image = SpreadsheetApp.newCellImage().setSourceUrl(fila[0]).toBuilder().build();
    sheet.getRange(i + 2, 3).setValue(image);
  } else {
    Logger.log(fila[0]);
    var blob = UrlFetchApp.fetch(fila[0]).getBlob();
    var id = DriveApp.createFile(blob.setName("temp")).getId();
    Utilities.sleep(2000);
    var { thumbnailLink } = Drive.Files.get(id);
    var image = SpreadsheetApp.newCellImage().setSourceUrl(thumbnailLink.replace("=s220", "=s1000")).toBuilder().build();
    sheet.getRange(i + 2, 3).setValue(image);
    DriveApp.getFileById(id).setTrashed(true);
  }
});

// rango.setValues(values); //  In this modification, this script is not used.