Retrieve file owner email of Google Drive files-CodePudding

I’m looking to use Drive API v3 with appscript to look up the owners of drive files by a list of file ids (listed in column A) and print the output to Column B.

Is this possible?

CodePudding user response：

If you are planning to use Apps Script you can use the following function:

function myFunction() {

var spreadsheetId = "###";
var ss = SpreadsheetApp.openById(spreadsheetId);
console.log(ss.getOwner().getEmail())

}

Please note that the code would give you error messages if you do not have access to the files. This is expected due to security reasons. Sharing permissions would need to be at least for anyone within the domain to have access to the file.

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 of the file and put it to column "B".
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().getEmail();
      return [owner];
    } catch (e) {
      console.log({id, error: e.message});
      return [""];
    }
  });
  range.offset(0, 1).setValues(values);
}

When this script is run, the file IDs are retrieved from the column "A", the owner (email address) of the file is retrieved, and the owner email address is put to the column "B".
When the owner cannot be retrieved, the empty value is put. At that time, you can see the error message in the log.

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 owner's email address cannot be retrieved. Please be careful about this.

If the file IDs in the sharead drive are included in the file ID list, you can also use the following script. In this case, please enable Drive API at Advanced Google services.

  function myFunction2() {
    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 obj = Drive.Files.get(id, {supportsAllDrives: true});
        const owner = obj.owners ? obj.owners[0].emailAddress : obj.sharingUser.emailAddress;
        return [owner];
      } catch (e) {
        console.log({id, error: e.message});
        return [""];
      }
    });
    range.offset(0, 1).setValues(values);
  }

In this case, when the file is put in the shared drive, the email address of the sharing user is retrieved.

Sample script:

Note:

References: