CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Nerea
Nerea

Reputation: 319

convert url with .svg extension to image in cell

I have column B which contains a url in each cell. I have column C that my intention is to be the url of the cell in column B converted into an image.

The urls with extension .png/.jpg converts them to images without problem.

The urls with the .svg extension cannot be converted into images.

And this is really a problem, because the urls are automatically synchronized from another platform to Google Sheets and they are practically all .svg extension.

The strangest thing is that with the formula "=IMAGE(urlCell)" the urls with .svg extension are converted into images without problem.

Also, I am getting the error

Exception: The parameters (Blob,String) don't match the method signature for SpreadsheetApp.Sheet.insertImage.

So what am I doing wrong?

This is my code:

function imagenIcono4() {

  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 {

      var url = fila[0];
      Logger.log(url);

      const blob = UrlFetchApp.fetch(url).getBlob();
      sheet.insertImage(blob, fila[1]);

    }
  }) 

  rango.setValues(values);
}

Upvotes: 1

Views: 425

Answers (1)

Tanaike
Tanaike

Reputation: 201533

It seems that unfortunately in the current stage, the SVG image data cannot be used for sheet.insertImage and SpreadsheetApp.newCellImage().setSourceUrl. By the way, the arguments of insertImage are blobSource, column, row and blobSource, column, row, offsetX, offsetY. If you want to use this, please be careful about this.

In your situation, I thought that this method (Author: me) might be able to be used.

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

In this case, Drive API is used. So, please enable Drive API at Advanced Google services.

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 {

    var url = fila[0];
    Logger.log(url);

    const blob = UrlFetchApp.fetch(url).getBlob();
    sheet.insertImage(blob, fila[1]);

  }
})

To:

values.forEach((fila) => {
  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);
  }
});

Upvotes: 3

Related Questions