Fix Downloaded Image Width & Height-CodePudding

I have a list of image URLs in my sheet. The problem is when I download the image in a cell its so small and I want to make it bigger also code stop's working if a bad URL is present.

}

function getImages(){

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let lastRow = sheet.getLastRow();
 
  for (let i = 0; i < lastRow-1; i  ){
  let url = sheet.getRange(2 i,10).getValues();
  let image = SpreadsheetApp.newCellImage().setSourceUrl(url);
   
    sheet.getRange(2 i,10).setValue(image);
    
  }

CodePudding user response：

Modification points:

About The problem is when I download the image in a cell its so small and I want to make it bigger, in this case, the image is put into a cell. So, how about increasing the cell width and height?
About code stop's working if a bad URL is present., When the URL is invalid URL, it seems that an error occurs at sheet.getRange(2 i, 10).setValue(image);.
In your script, getValues and setValues are used in a loop. In this case, the process cost will become high. Ref

When these points are reflected in your script, how about the following modification?

Modified script:

function getImages() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let lastRow = sheet.getLastRow();

  // I modified below script.
  // 1. Retrieve URLs from cells "J2:J".
  const range = sheet.getRange("J2:J"   lastRow);
  const values = range.getValues();
  
  // 2. Check URLs.
  const res = UrlFetchApp.fetchAll(values.map(([j]) => ({ url: j, muteHttpExceptions: true })));
  
  // 3. Create an array including CellImage object.
  const v = res.map((r, i) => [r.getResponseCode() == 200 ? SpreadsheetApp.newCellImage().setSourceUrl(values[i][0]).build() : null]);
  
  // 4. Put the array.
  range.setValues(v);
  
  // 5. Increase row height and column width.
  sheet.setRowHeights(2, lastRow - 1, 100).setColumnWidth(10, 500);
}

In this modification, the following flow is run.
1. Retrieve URLs from cells "J2:J".
2. Check URLs.
3. Create an array including CellImage object.
4. Put the array.
5. Increase row height and column width.
When you want to adjust the row height and column width, please modify sheet.setRowHeights(2, lastRow - 1, 100).setColumnWidth(10, 500);.
In this sample, the invalid URL is converted to null. If you want to leave the URL, please modify null to values[i][0].

Note:

When your script is modified, the following modification can be also used. In this case, try-catch is used.

  function getImages() {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    let lastRow = sheet.getLastRow();
    for (let i = 0; i < lastRow - 1; i  ) {
      let url = sheet.getRange(2   i, 10).getValue();
      let image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();
      try {
        sheet.getRange(2   i, 10).setValue(image);
      } catch (e) {
        console.log(e.message)
      }
    }
    sheet.setRowHeights(2, lastRow - 1, 100).setColumnWidth(10, 500);
  }

Modification points:

Modified script:

Note:

References: