Accessing a private Google sheet with a google cloud service account-CodePudding

I have a private Google Spreadsheet and I’m trying to access it programmatically using Google Visualization/Google Charts. I've created the service account, and I have tried using the google-auth-library and googleapis npm packages to create an access token that I can then use to access the spreadsheet. But, when I try to use that access token to read from the spreadsheet, the request fails with HTTP code 401 (Unauthorized). What do I need to do in order to make this work?

This is my code:

const { auth } = require('google-auth-library');
const keys = require('./jwt.keys.json');

const id = '{Spreadsheet ID}';
const sheetId = '{Sheet ID}';
const query = "{Query}";

async function getClient(){
    const client = auth.fromJSON(keys);
    client.scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
    console.log(client);
    return client;
}

async function main(){
    const client = await getClient();
    const token = await client.getAccessToken();
    console.log(token);
    console.log(token.token);
    const url = `https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:csv&tq=${encodeURIComponent(query)}&access_token=${token.token}#gid=${sheetId}`;
    const res = await client.request({url});
    console.log(res);
}

main().catch(console.error);

CodePudding user response：

When I saw your script, I thought that it is required modifying the scope. I had got the same situation as you (the status code 401 using your endpoint). Unfortunately, it seems that your endpoint cannot be used using the scope of https://www.googleapis.com/auth/spreadsheets.readonly. So, for example, how about changing it as follows, and testing it again?

From:

https://www.googleapis.com/auth/spreadsheets.readonly

To:

https://www.googleapis.com/auth/spreadsheets

https://www.googleapis.com/auth/drive.readonly

Note:

When I tested your endpoint using the modified scope, I confirmed that no error occurred. But if you tested it and when an error occurs, please check other part, again.