Trying to save a copy of a file to specific Google Drive folder, from URLs in a Google Sheet

Question

I am running this code in google Apps Script to save a copy of a file (photo) in a specific folder, from a URL link extracted from a Google Sheet. The photos are in other peoples google drive accounts..

This google sheet comes from ODK form that different people fill in during field work surveys, and i need to centralize all the data to my Gdrive.

The URLs are in colum L of this spreadsheet https://docs.google.com/spreadsheets/d/14vtEHDxxNc0d4yEn4T0gZcEyePJeJgw6NqQ9ajmn1fo/view#gid=0

The folder ID i am trying to save files to is:"1ImYcc_HqFZjpCUj4BHprhu3HSXKNQS0E"

I run my code but nothing happens. (I have turned the API on)

I am new to coding so i am very lost.

var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Sheet1";
  var sh = ss.getSheetByName(sheetName);
  var rangeA1 = "L6:L68";
  var rg=sh.getRange(rangeA1);


  var formulas = rg.getFormulas();

  for (var i in formulas) {
    for (var j in formulas[i]) {
      var formula = formulas[i][j];
      if (formula.length !=0){

        var regex = /=\w+\((.*)\)/i;
        var matches = formula.match(regex);
        var imgurl = matches[1];
        var filename = imgurl.substring(imgurl.lastIndexOf("/") + 1, imgurl.lastIndexOf(""));
        Logger.log(filename); 

 var image = UrlFetchApp.fetch(imgurl).getBlob().getAs('image/jpg').setName(filename);

        var folder = DriveApp.getFolderById("1ImYcc_HqFZjpCUj4BHprhu3HSXKNQS0E");
        var file = DriveApp.createFile(image);
        Drive.Files.update({"parents": [{"id": folder.getId()}]}, file.getId());
      }
    }
  }
}```

Answer 1

Let me know if this works for you:

SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(`Sheet1`)
              .getRange(`L6:L68`)
              .getDisplayValues()
              .flat()
              .filter(String)
              .forEach(url => {
                const targetFile = url.slice(url.indexOf(`id=`)+3)
                try {
                  DriveApp.getFileById(targetFile)
                          .makeCopy()
                          .moveTo(`1ImYcc_HqFZjpCUj4BHprhu3HSXKNQS0E`)
                        //.setName("")
                } catch (e) { Logger.log(`Unauthorized Access: [${targetFile}]`)}
              })