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}]`)}
              })

Answer 2

Copy file to folder

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getDisplayValues().filter(r => r[11]);
  const folder = DriveApp.getFolderById("1ImYcc_HqFZjpCUj4BHprhu3HSXKNQS0E");
  vs.forEach(r => {
    let m = r[11].toString().match(/^[^=]+=([0-9A-Za-z_-]+)$/g);
    if (m) {
      let f = DriveApp.getFileById(m[1]);
      let c = f.makeCopy(f.getName());
      Drive.Files.update({"parents": [{"id": folder.getId()}]}, c.getId());
    }
  })
}