Search code examples
google-sheetsservice-accounts

Getting shared with service account info or retrieve all docs shared with a specific service account?

I'm looking for a way to either

1) Read/retrieve share notifications whenever a Sheet is shared with a specific service account

or

2) Get a list of all Sheets shared with a specific service account

Background: Users duplicate an existing Sheet template, modify its contents and share it with my service account email so I can retrieve the Sheet data programmatically. This still requires the users to input the resulting share link into my backend after sharing.

Instead I'd prefer using the API to receive either something like a "shared with service account" webhook event or an option to read all Sheets shared with this service account.

Does this require GSuite, or is there an API/webhook to achieve this?

Solution

  • Answer:

    Yes, you can retrieve this information with the Drive API.

    More Information:

    If you make a Drive: files.list call as a service account, it will return the files of the Service Account's Drive.

    If your users are sharing Sheets with the Service Account, you can retrieve them by making an API call to this method with the sharedWithMe flag set to true, and the mimeType set to application/vnd.google-apps.spreadsheet in the q parameter.

    JavaScript example:

    function execute() {
    return gapi.client.drive.files.list({
      "q": "sharedWithMe and mimeType = 'application/vnd.google-apps.spreadsheet'"
    })
        .then(function(response) {
                // Handle the results here (response.result has the parsed body).
                console.log("Response", response);
              },
              function(err) { console.error("Execute error", err); });
  }

    References: