0

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());
      }
    }
  }
}```
Lucas Alegretti
  • 3
  • 3

2 Answers2

0

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}]`)}
              })
NEWAZA
  • 1,536
  • 2
  • 4
  • 19
  • Your code executed completly but i still did not work. the folder is still empty. Actually It did work, i got a copy of the photos into my Gdrive, they are not in the specifieds folder but, is think it is easy for me just to move them now... thank you so much – Lucas Alegretti May 24 '22 at 21:34
-1

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());
    }
  })
}
Cooper
  • 59,616
  • 6
  • 23
  • 54