GDrive Files to CSV List

FileList2CSV.js

/* FileList2CSV
 *
 * This AppScript produces a Salesforce DataLoader-ready spreadheet that lists all files contained in a given Google Drive Folder.
 * The script fetches file names and extracts ID's (via FILENAME_REGEX) to place into a spreadsheet
 * Once the files are downloaded to a local machine, the resulting local file paths can be updated in VersionData and PathOnClient columns before download as CSV
 * If files need to relate to a Salesforce record, Salesforce ID's should be entered into FirstPublishedLocationId
 *
 * Additional details on uploading files via DataLoader can be found at https://help.salesforce.com/articleView?id=000314772&type=1&mode=1
*/
var GDRIVE_FOLDER_ID = PropertiesService.getScriptProperties().getProperty('folderId');
var FOLDER = DriveApp.getFolderById(GDRIVE_FOLDER_ID);
var SUB_FOLDER_NAME = 'File List';
var DATETIME = dateTime(new Date());
var SHEET = SpreadsheetApp.create('File List_' + DATETIME);
var FILENAME_REGEX = /legacy_candidate_id=(\d+)/g;

function run() {
  // check if subfolder exists, if not, create it
  var subFolder = getSubFolder(SUB_FOLDER_NAME);
  // move new sheet into subfolder
  DriveApp.getFileById(SHEET.getId()).moveTo(subFolder);
  // add headers to sheet
  SHEET.appendRow(createHeaders());
  
  // iterate through the list of files in parent folder, add rows
  var files = FOLDER.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    SHEET.appendRow([file.getName(), // title
                     parseCandidateId(file.getName()),  // description
                     '', // versionData
                     '',  // PathOnClient
                     '' // FirstPublishedLocationId
                    ]);
  }
}

/* returns a Folder object for the subfolder that contains a list of files */
function getSubFolder(folderName) {
  // create subfolder if not exists
  var fileList = FOLDER.getFoldersByName(folderName);
  // check if folder exists, if so, return Folder
  if (fileList.hasNext()) {
    return fileList.next();
  }
  // folder does not exist, create it and return it
  else {
    return FOLDER.createFolder(folderName);
  }
}

function createHeaders() {
  return ['Title','Description','VersionData','PathOnClient','FirstPublishedLocationId'];
}

/* Given a filename and a global regex, returns the ID or key value. If file does not match the pattern, returns empty string */
function parseCandidateId(fileName) {
  var matchArray = [...fileName.matchAll(FILENAME_REGEX)];
  Logger.log(matchArray);
  return matchArray.length > 0 ? matchArray[0][1] : '';
}

/* constructs a date string in yyyymmdd_hhmmss format*/
function dateTime(date) {
  return date.getFullYear().toString() + pad2(date.getMonth() + 1) + pad2( date.getDate()) + '_' + pad2(date.getHours()) + pad2(date.getMinutes()) + pad2( date.getSeconds());
}

/* used to add leading zero's to numbers that are less than 10
* input: 1
* output: 01
*/
function pad2(n) { return n < 10 ? '0' + n : n }