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