Use google-spreadsheets with nodejs with service account?

user_3328759 0
Viewed 1279

How can use a service account with nodejs in order to access at a sheet on google with google api?

I tried with the nodejs (npm) module google-spredsheet but when the loadInfo() it returns always Request failed error 404

edit1: ii already have a service account and the key file downloaded and in my directory. Here's the actual script:


const express     = require("express"),
{GoogleSpreadsheet} = require('google-spreadsheet'),
creds             = require('./client_secret.json'),
app             = express();

app.set("view engine", "ejs");
app.use(express.static(__dirname+"/public"));

app.get("/google-spreadsheet", async function(req, res){
  
  // Identifying which document we'll be accessing/reading from
  
  var doc;
  
    doc = new GoogleSpreadsheet('1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs/edit#gid=0');

  // Authentication
    await doc.useServiceAccountAuth(creds); 
  
    await doc.loadInfo(); // loads document properties and worksheets
    console.log("test: "+doc.title);

});


app.listen(8080, () => {
  console.log('[EXPRESS] Web Server active on: ' + 8080);
});
node.js google-sheets-api google-sheets
1 Answers

I believe your current situation and goal as follows.

  • Your client_secret.json is for the service account.
  • You are using "google-spreadsheet" of the latest version.
  • You want to access to the Google Spreadsheet using "google-spreadsheet" with the service account.

Modification points:

  • In your script, '1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs/edit#gid=0 is used as the Spreadsheet ID. In this case, such error occurs. I think that this might be the reason of your issue. So please modify it to '1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs'.

Modified script:

When your script is modified, it becomes as follows.

From: 
doc = new GoogleSpreadsheet('1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs/edit#gid=0');
To: 
doc = new GoogleSpreadsheet('1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs');

Note:

  • When the Google Spreadsheet of 1XKcnyJkTyq15AlvhAs3V_2wnP8vgwY3mWXAijyiINVs is not shared with the email of the service account, the service account cannot use the Spreadsheet. Please be careful this.

Reference:

user_71086530
Related