Searching for files based on array of file names and retrieving file properties to be logged in a sh-CodePudding

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.

CodePudding user response：

Update Invoice Data

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);
}

bitwise not (~)

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