I have been trying to solve this problem for the past couple of days but I am unable to succeed. I need to generate a url, which asks the user permission to read a particular Google Sheet of theirs, and if they agree, I will have access to read that sheet.
I have read and re-read the Google Sheet API here, and the Google REST API for Node here, but I am having trouble applying it to my particular case.
I was able to successfully use the REST API guide to read the meta details about all the files in the user's Google Drive, but what I actually need is to read (and only read, no other permission) just ONE google sheet. According to the Sheet API, the correct scope is https://spreadsheets.google.com/feeds, but that was all the information I could use from the API because the code is only written in Java/ .NET.
I have scoured the forums for answers,but most of them were very outdated and not applicable, I found this post which suggested using node-google-spreadsheets, but the documentation does not provide enough information as how to actually use it. I have attached my code below. Along with annotations as to where I am having problems with. I would appreciate ANY help as I have exhausted myself trying to make this to work. Thank you (:
var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
var GoogleSpreadsheets = require('google-spreadsheets');
var SCOPES = ['https://www.googleapis.com/auth/drive.metadata.readonly']; //This scope works just fine when reading metadata, but to readspreedsheets, I think the correct one is: https://spreadsheets.google.com/feeds
var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
process.env.USERPROFILE) + '/.credentials/';
var TOKEN_PATH = TOKEN_DIR + 'drive-nodejs-quickstart.json';
fs.readFile('client_secret.json', function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
authorize(JSON.parse(content), listFiles);
});
/*************************************No Idea where to put this code:
var oauth2Client = new google.auth.OAuth2(CLIENT_ID, CLIENT_SECRET, REDIRECT_URL);
// Assuming you already obtained an OAuth2 token that has access to the correct scopes somehow...
oauth2Client.setCredentials({
access_token: ACCESS_TOKEN,
refresh_token: REFRESH_TOKEN
});
GoogleSpreadsheets({
key: '<spreadsheet key>',
auth: oauth2Client
}, function(err, spreadsheet) {
spreadsheet.worksheets[0].cells({
range: 'R1C1:R5C5'
}, function(err, cells) {
// Cells will contain a 2 dimensional array with all cell data in the
// range requested.
});
});
************************************************************/
function authorize(credentials, callback) {
var clientSecret = credentials.installed.client_secret;
var clientId = credentials.installed.client_id;
var redirectUrl = credentials.installed.redirect_uris[0];
var auth = new googleAuth();
var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
fs.readFile(TOKEN_PATH, function(err, token) {
if (err) {
getNewToken(oauth2Client, callback);
} else {
oauth2Client.credentials = JSON.parse(token);
callback(oauth2Client);
}
});
}
function getNewToken(oauth2Client, callback) {
var authUrl = oauth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES
});
console.log('Authorize this app by visiting this url: ', authUrl);
var rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
rl.question('Enter the code from that page here: ', function(code) {
rl.close();
oauth2Client.getToken(code, function(err, token) {
if (err) {
console.log('Error while trying to retrieve access token', err);
return;
}
oauth2Client.credentials = token;
storeToken(token);
callback(oauth2Client);
});
});
}
function storeToken(token) {
try {
fs.mkdirSync(TOKEN_DIR);
} catch (err) {
if (err.code != 'EEXIST') {
throw err;
}
}
fs.writeFile(TOKEN_PATH, JSON.stringify(token));
console.log('Token stored to ' + TOKEN_PATH);
}
function listFiles(auth) {
var service = google.drive('v3');
service.files.list({
auth: auth,
pageSize: 10,
fields: "nextPageToken, files(id, name)"
}, function(err, response) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
var files = response.files;
if (files.length == 0) {
console.log('No files found.');
} else {
console.log('Files:');
for (var i = 0; i < files.length; i++) {
var file = files[i];
console.log('%s (%s)', file.name, file.id);
}
}
});
}
Edit1: With the help of Sumnu2, I incorporated the google-spreadsheets (previously commented out). When I try to run the app, I get the following error: Not authorized to view the sheet. This makes sense because the sheet is private and I need to generate a link which allows me access to it. I have posted the code on haste bin here
