Description
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
- First lambda creates the sheet if need be then locks it (owner is system user), then the first lambda invokes the second.
- 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' });
}