I've been trying to solve this but am new to programming. I want to get all file names from a temporary folder and filter them against file names that have previously been logged to a sheet together with other file properties (size, date modified, id, url).
After keeping only the file names in the array that aren't already listed, I'd like to retrieve the files that have these names from my tempfolder and get their file properties in order to append a new row to the sheet with the name, size, date modified, id, url).
This is what I have so far:
const invoiceLog = SpreadsheetApp.openById('SPREADSHEETID').getSheetByName('invoicedata');
const invoiceDispatcherLog = SpreadsheetApp.openById('SPREADSHEETID').getSheetByName('invoicedispatcherlog');
const tempInvoiceFolder = DriveApp.getFolderById('TEMPFOLDERID');
const invoiceFolder2021 = DriveApp.getFolderById('INVOICEFOLDERID');
function invoiceLogger () {
// get new Invoices from tempFolder and make an array of their names
let newInvoiceFileNames = [];
let newInvoices = tempInvoiceFolder.getFiles();
while (newInvoices.hasNext()){
file =newInvoices.next();
let row = []
row.push(file.getName())
newInvoiceFileNames.push(row);
Logger.log(row)
}
// filter the array of new invoices by existing invoice names in the invoice log
let newInvoiceFileNamesFlat = newInvoiceFileNames.map(function(row) {return row [0];});
let existingInvoices = invoiceLog.getRange('A2:A').getValues();
let existingInvoicesFlat = existingInvoices.map(function(row) {return row [0];});
var cleanInvoiceNames = newInvoiceFileNamesFlat.filter(item => !existingInvoicesFlat.includes(item))
//let markedFiles = DriveApp.getFilesByName(cleanInvoiceNames);
cleanInvoiceNames.forEach(SearchFiles)
I'm sure I have some redundancies in here and am not at a point where I can fully appreciate all aspects of how functions and methods interlock but would be enormously grateful for some guidance. Please let me know if any of this was unclear and if I can provide further info.
function updateInvoiceData() {
const ss = SpreadsheetApp.openById('ssid');
const ish = ss.getSheetByName('invoicedata');
const lnames = ish.getRange(2, 1, ish.getLastRow() - 1).getValues().filter(r => r[0]).flat();//listed names
const ifldr = DriveApp.getFolderById('TEMPFOLDERID');
let info = [];//new names
let ifiles = ifldr.getFiles();
while (ifiles.hasNext()) {
let ifile = ifiles.next();
let n = ifile.getName();
//if not already listed
if(!~lnames.indexOf(n)) {
info.push([n, ifile.getSize(),ifile.getDateCreated(),ifile.getId(),ifile.getUrl()]);
}
}
ish.getRange(ish.getLastRow() + 1,1,info.length,info[0].length).setValues(info);
}
This gets the modified date:
function updateInvoiceData() {
const ss = SpreadsheetApp.openById(gobj.globals.ssid);
const ish = ss.getSheetByName('Sheet0');
const sr = 2;//data start row
const lr = ish.getLastRow();//data last row
let lnames;
if(lr > 1) {
lnames = ish.getRange(2, 1, ish.getLastRow() - 1).getValues().filter(r => r[0]).flat();//listed names
} else {
lnames = [];
}
const ifldr = DriveApp.getFolderById(gobj.globals.testfolderid);
let info = [];//new names
let ifiles = ifldr.getFiles();
while (ifiles.hasNext()) {
let ifile = ifiles.next();
let n = ifile.getName();
//if not already listed
if(!~lnames.indexOf(n)) {
let d = JSON.parse(Drive.Files.get(ifile.getId())).modifiedDate;
info.push([n, ifile.getSize(),d,ifile.getId(),ifile.getUrl()]);
}
}
ish.getRange(lr + 1,1,info.length,info[0].length).setValues(info);
}
You have to enable Drive API version 2