Sheet fails to unlock despite success response from drive client

[NickDario]

Hello! I am attempting to lock two spreadsheets, run a process, then unlock them and update their data.
I am using typescript and the node client from @googleapis/drive running on node 16 in AWS Lambda.

I don't receive any errors from the lock and unlock requests, but after running the process 1-3 times sequentially, I eventually receive an error Google API error - [403] The caller does not have permission when updating the sheet.

If I remove the locking code and everything works fine and the sheet will update as expected - however i need the sheets to be locked so users don't modify it while the process is running.

I tried adding a delay after the locking code but that didn't seem to help. it worked somewhat more reliably when I removed the oauth singleton and just instantiated it each time, which makes me suspect that could be related. However the error still occurred just after 3-5 runs. After locking, the owner is the system user so I am unable to unlock the file, however after 5-10 minutes the file will unlock on its own.

This process is running in two lambda functions that run sequentially

1. First lambda creates the sheet if need be then locks it (owner is system user), then the first lambda invokes the second.
2. Second lambda runs the process then unlocks the sheet

Are there any known bugs / specific idiosyncrasies with locking and unlocking spreadsheets that may account for this behavior? What is somewhat baffling is the file will eventually unlock after 10 minutes after it enters the locked state.

Example Error:

{
    "message": "Google API error - [403] The caller does not have permission",
    "name": "AxiosError",
    "stack": "webpack://migrations-handler-ts/shared/node_modules/axios/dist/node/axios.cjs:1913\n    reject(new AxiosError(\n           ^\n\nAxiosError: Google API error - [403] The caller does not have permission\n    at settle (webpack://migrations-handler-ts/shared/node_modules/axios/dist/node/axios.cjs:1913:12)\n    at Unzip.<anonymous> (webpack://migrations-handler-ts/shared/node_modules/axios/dist/node/axios.cjs:3008:11)\n    at Unzip.emit (node:events:525:35)\n    at Unzip.emit (node:domain:489:12)\n    at endReadableNT (node:internal/streams/readable:1358:12)\n    at processTicksAndRejections (node:internal/process/task_queues:83:21)",
    "config": {
        "transitional": {
            "silentJSONParsing": true,
            "forcedJSONParsing": true,
            "clarifyTimeoutError": false
        },
        "adapter": [
            "xhr",
            "http"
        ],
        "transformRequest": [
            null
        ],
        "transformResponse": [
            null
        ],
        "timeout": 0,
        "xsrfCookieName": "XSRF-TOKEN",
        "xsrfHeaderName": "X-XSRF-TOKEN",
        "maxContentLength": null,
        "maxBodyLength": null,
        "env": {
            "Blob": null
        },
        "headers": {
            "Accept": "application/json, text/plain, */*",
            "Content-Type": "application/x-www-form-urlencoded",
            "Authorization": "Bearer [redacted]",
            "User-Agent": "axios/1.6.0",
            "Accept-Encoding": "gzip, compress, deflate, br"
        },
        "baseURL": "https://sheets.googleapis.com/v4/spreadsheets/sheet1_ID",
        "paramsSerializer": {},
        "method": "post",
        "url": "/values/'Invalid%20Patients':clear",
        "params": {}
    },
    "code": "ERR_BAD_REQUEST",
    "status": 403
}

simplified version of the code:

import { drive, drive_v3 } from '@googleapis/drive';
import { OAuth2Client } from 'google-auth-library';

type OAuth2ClientCredentials = {
  oAuthClientID: string;
  oAuthClientSecret: string;
  refreshToken: string;
};

function getCredentialsFromVault(): OAuth2ClientCredentials {
  return {
    oAuthClientID: 'XXXXX',
    oAuthClientSecret: 'XXXXX',
    refreshToken: 'XXXXX',
  };
}

let oauthClient: OAuth2Client;
async function genGoogleDriveOauthClient(): Promise<OAuth2Client> {
  if (oauthClient == null) {
    const credentials = getCredentialsFromVault();
    oauthClient = new OAuth2Client({
      clientId: credentials.oAuthClientID,
      clientSecret: credentials.oAuthClientSecret,
    });
    oauthClient.setCredentials({ refresh_token: credentials.refreshToken });
  }

  return oauthClient;
}

async function genLockGoogleDriveFile({
  oauthClient,
  fileID,
  reason,
}: {
  oauthClient: OAuth2Client;
  fileID: string;
  reason?: string;
}): Promise<{ data?: drive_v3.Schema$File; status?: number; statusText?: string }> {
  const googleDrive = drive({ version: 'v3', auth: oauthClient });
  const contentRestriction = {
    readOnly: true,
    reason,
  };
  const response = await googleDrive.files.update({
    fileId: fileID,
    fields: 'contentRestrictions',
    requestBody: {
      contentRestrictions: [contentRestriction],
    },
    supportsAllDrives: true,
  });
  return { data: response.data, status: response.status, statusText: response.statusText };
}

async function genUnlockGoogleDriveFile({
  oauthClient,
  fileID,
}: {
  oauthClient: OAuth2Client;
  fileID: string;
}): Promise<{ data?: drive_v3.Schema$File; status?: number; statusText?: string }> {
  const googleDrive = drive({ version: 'v3', auth: oauthClient });
  const contentRestriction = {
    readOnly: false,
  };
  const response = await googleDrive.files.update({
    fileId: fileID,
    fields: 'contentRestrictions',
    requestBody: {
      contentRestrictions: [contentRestriction],
    },
    supportsAllDrives: true,
  });
  return { data: response.data, status: response.status, statusText: response.statusText };
}

async function main() {
  const oauthClient = await genGoogleDriveOauthClient();
  //  Executes on cloud function 1
  await genLockGoogleDriveFile({ oauthClient, fileID: 'sheet1_ID' });
  await genLockGoogleDriveFile({ oauthClient, fileID: 'sheet2_ID' });

  //  try waiting to see if it can unlock
  await new Promise((resolve) => setTimeout(resolve, 15 * 1000));

  //  executes on cloud function 2 invoked at end of cloud function 1
  await genUnlockGoogleDriveFile({ oauthClient, fileID: 'sheet1_ID' });
  await genUnlockGoogleDriveFile({ oauthClient, fileID: 'sheet2_ID' });
}

[CoryMci]

I'm running into a similar issue using the Drive (v3) and Sheets (v4) APIs. The first time this issue was noticed was about 2-3 weeks ago. Before then, we had been running smoothly for several months prior with the same process.

Our process is similar, we are unlocking a file with the Drive API:

await this.drive.files.update({
  fileId,
  requestBody: {
    contentRestrictions: [{ readOnly: false }],
    appProperties: { locked: 'false' },
  },
});

Followed by batch operation with the sheets API after a successful response:

await this.sheets.spreadsheets.batchUpdate({
      spreadsheetId,
      requestBody: {
        requests,
      },
    });

The batch update will throw the error:

"status": 403,
"code": 403,
"errors": [
  {
    "message": "The caller does not have permission",
    "domain": "global",
    "reason": "forbidden"
  }
]

I've tried to add delays / exponential backoff, but as you described the delays can be up to 10 minutes. Oddly enough, the "locked" appProperty still seems to be updated immediately, but the contentRestriction is delayed. I'm also curious if there are any known issues with the drive API that may be causing the delay.

If it helps, our temporary workaround is to simply change all writer permissions to reader. I know it's far from ideal, but in our case it keeps the app usable.