Retrieve drive file information by ID with appscript-CodePudding

Retrieve drive file information of Google Drive files and list in Google Sheet

I’m looking to use appscript to look up the owners, file created date, current sharing settings of drive files by a list of file ids, with file IDs listed in column A, row by row of the sheet and the outcome would print the informations in separate columns in the row (column B for owner, C for created date, D for current sharing settings).

Is this possible?

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the file IDs from the column "A" of the Spreadsheet and want to retrieve the owner's email, the created date, the current sharing settings (the value of "Enum Access") of the file and put it to column "B", "C" and "D".
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet name, and run myFunction at the script editor.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  if (lastRow == 0) return;
  const range = sheet.getRange("A1:A"   lastRow);
  const values = range.getValues().map(([id]) => {
    try {
      const file = DriveApp.getFileById(id);
      const owner = file.getOwner();
      const createdDate = file.getDateCreated();
      const sharing = file.getSharingAccess().toString();
      return [owner ? owner.getEmail() : "", createdDate, sharing];
    } catch (e) {
      console.log(e.message)
      return Array(3).fill("");
    }
  });
  range.offset(0, 1, values.length, values[0].length).setValues(values);
}

When this script is run, the file IDs are retrieved from the column "A", the owner (email address), the created date, the sharing setting of the file is retrieved, and those values are put from the column "B".
When the owner cannot be retrieved, the empty value is put.

Note:

In this sample script, from your question, it supposes that the file IDs are put to the column "A". If the column "A" is not the file ID, the file metadata cannot be retrieved. Please be careful about this.

Sample script:

Note:

References: