Apps Script to Compare a list of file names in google drive to a list of names in a google sheet col-CodePudding

I have as apps script that writes the file name and URL of a google drive folder to a specified sheet. In its current form it appends the full contents of the drive folder to the sheet. What I need it to do is first compare the file names that already exist on the sheet to the file names that are in drive, and then only append the files that are not already on the sheet.

Here is the code i am trying to use to compare the two lists and only add the names that do not already exist.

function listFolderContents() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('files');
  const folder = DriveApp.getFolderById('1ShsR9jQAkTCeezouk6sW0NnPpo-kHFjK');
  const contents = folder.getFiles();
  const existsOnSheet = sheet.getRange(1,1,sheet.getLastRow(),1).getValues();
  //console.log(existsOnSheet);
  
  //I want to only append rows with names that do not already exist
  
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    if (name === existsOnSheet){
      return
      }
    else{
    sheet.appendRow( [name, link] );
    }     
  }  
}

CodePudding user response：

Probably you should replace this:

if (name === existsOnSheet)

with this:

if (existsOnSheet.includes(name))

And add flat() here:

const existsOnSheet = sheet.getRange(1,1,sheet.getLastRow(),1).getValues().flat();

Just in case:

var old_list = ['a', 'b', 'c']
var new_list = ['a', 'b', 'c', 'd', 'e']
var new_files = new_list.filter(x => !old_list.includes(x))

console.log(new_files) // output: ['d', 'e']

And don't forget that a folder on Google Drive can contain files with the same names. Perhaps it makes sense to use IDs or URLs to identify files in a folder.

CodePudding user response：

Final working script:

// replace your folder below with the folderID for which you want a listing
function listFolderContents() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('index');
  const folder = DriveApp.getFolderById('1ShsR9jQAkTCeezouk6sW0NnPpo-kHFjK');
  const contents = folder.getFiles();
  const existsOnSheet = sheet.getRange(2,1,sheet.getLastRow(),1).getValues().flat();
  //console.log(existsOnSheet);
  
  //I want to only append rows with names that do not already exist
  
  var file;
  var name;
  var link;
  //var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    if (existsOnSheet.includes(name))
{return
    } 
    else {
    sheet.appendRow( [name, link] );

    }    
  } 
}```