How to log Google Drive File Names to Google Sheets using Google Apps Script

1
votes

So I am trying to log files (and their Id) from my Drive to a spreadsheet using Google Apps Script. The problem is that I don't know how to add content to rows of a Google Sheets document.

I know how to log my files and their Id:

var files = DriveApp.getFiles();
while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName());
    Logger.log(file.getId());
}

Now I would like to log the Name and Id of my files from one folder to a Google sheet. Does anybody know how to achieve this?

1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-drive-api

1 Answers

1
votes

You'll need to get the spreadsheet, and add the file names to a 2D array. The following code requires you to enter the spreadsheet file ID and the sheet tab name.

function logFilesToSheet() {
  var arrayForOneRow,file,id,name,outerArray,sh,ss;

  id = "";//Enter your spreadsheet file id here
  name = "";//Enter the sheet tab name here

  ss = SpreadsheetApp.openById(id);
  sh = ss.getSheetByName(name);

  outerArray = [];

  var files = DriveApp.getFiles();

  while (files.hasNext()) {
    file = files.next();

    //Logger.log(file.getName());
    //Logger.log(file.getId());

    arrayForOneRow = [];//Reset on each loop - Each row needs a new array

    arrayForOneRow.push(file.getName());
    arrayForOneRow.push(file.getId());

    outerArray.push(arrayForOneRow);
  }

  sh.getRange(sh.getLastRow() + 1,1,outerArray.length,2).setValues(outerArray);
}