CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-drive-api
Maneesh Singhal
Maneesh Singhal

Reputation: 9

Get a List of Drive Files Into a Google Sheet

  var folder = DriveApp.getFolderById('1_gA4D7dfybJ60IdfgsnqdfdsgVoo9D76fgsdgf9cqmAnJI7g7');
  var contents = folder.getFiles();
  var file;
  var name;
  var sheet = SpreadsheetApp.getActiveSheet();
  var date;
  var size;

  sheet.clear();
  sheet.appendRow(["Nome", "Data", "Dimensione"]);

  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    date = file.getDateCreated();
    size = file.getSize();
    id = file.getUrl();

    data = [name, date, size,id]
    sheet.appendRow(data);
  }
};

Every time all the data is cleared, after that the data is added from the starting.

sheet.clear();sheet.appendRow(["Nome", "Data", "Dimensione"]);

Is there any way to check the data?
if the same data present Already then the function does not work on the row.

If a new record is added, it should also be added, but the function does not start again from one.

If I have 20 PDFs in my folder, and add one more PDF in folder, then add the function 21 pdf and do not run the function from the beginning.

Upvotes: 0

Views: 121

Answers (1)

Marios
Marios

Reputation: 27390

Explanation:

The logic behind the following script is the following:

  1. We get all the URLs of the files that are currently in column D of the sheet. These are the URLs of the files that have been recorder so far. We can safely assume that the URLs are always unique:

    const aURLs = sheet.getRange('D2:D'+sheet.getLastRow()).getValues().flat();

  1. The second step is to iterate through the files as the original script would do and check if the URL of a file is in aURLs. If the file URL is not in aURLs or in other words in column D, then add it to the newFiles array:

    if(!uNames.includes(name)){
 newFiles.push(data);
 }

  2. After we checked all the files, we can add, after the last row of sheet, only the new files:

sheet.getRange(sheet.getLastRow()+1,1,newFiles.length,newFiles[0].length).setValues(newFiles);

Solution:

Manually add the headers in the file for the first time only:

headers

and then execute the script every next time:

function myFunction() {
  const folder = DriveApp.getFolderById('1_gA4D7dfybJ60IdfgsnqdfdsgVoo9D76fgsdgf9cqmAnJI7g7');
  const contents = folder.getFiles();
  const sheet = SpreadsheetApp.getActiveSheet();
  const aURLs = sheet.getRange('D2:D'+sheet.getLastRow()).getValues().flat();
  
  const newFiles = [];
  
  while(contents.hasNext()) {
    let file = contents.next();
    let name = file.getName();
    let date = file.getDateCreated();
    let size = file.getSize();
    let id = file.getUrl();
    let data = [name, date, size,id]
    if(!aURLs.includes(id)){
    newFiles.push(data);
    }
  }
  sheet.getRange(sheet.getLastRow()+1,1,newFiles.length,newFiles[0].length).setValues(newFiles);
}

Upvotes: 2

Related Questions