Offload glideapps picture

glidePicsOffloading.gs

function glidePicsOffloading() {

    var range = "G:I";
    var publicDriveFolderId = "__google drive folder id___";

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // folder where pictures are located
    var picsfolder = DriveApp.getFolderById(publicDriveFolderId);

    // get ranges of cell where we have to offloading glideapps pictures
    var sheet = ss.getSheets()[0];
    var cells = sheet.getRange(range);

    // we loop across all pictures
    forEachRangeCell(cells, (cell) => {
      var value = cell.getValue();

      // glideapps storage contains string glide-prod
      if(value.indexOf('glide-prod') !== -1){

        // override with empty preventing another trigger to duplicate this action
        // + flush to immediate action
        cell.setValue('');
        SpreadsheetApp.flush();
        
        // get image name
        var blobname = value.substring(value.lastIndexOf('/') +1 );

        // check if filename already exists (some items have same picture)
        var fileIterator = picsfolder.getFilesByName(blobname);
        var fileExists = fileIterator.hasNext();
        var fileUrl = ""; // final url to the picture
        
        if(fileExists === false){
          // file not found in google drive => we download it, resize it and upload it
          var response = UrlFetchApp.fetch(value);
          var pic = picsfolder.createFile(response.getBlob().setName('original_' + blobname))

          var resizePicture = ImgApp.doResize(pic.getId(), 1080);
          pic = picsfolder.createFile(resizePicture.blob.setName(blobname));

          fileUrl = getShortFileUrl(pic.getUrl());
        } else {
          // file found, we just grab the url
          fileUrl = getShortFileUrl(fileIterator.next().getUrl());
        }

        console.log(blobname + "->" + fileUrl);

        // we finally update the cell value with the new url
        cell.setValue(fileUrl);
      }
    });
}

function getShortFileUrl(fileUrl){
  return fileUrl.substring(0, fileUrl.lastIndexOf('/'));
}

function forEachRangeCell(range, f) {
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();

  for (let i = 1; i <= numCols; i++) {
    for (let j = 1; j <= numRows; j++) {
      const cell = range.getCell(j, i)

      f(cell)
    }
  }
}