So far I have written this code:
var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/sheets.googleapis.com-nodejs-quickstart.json
var SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
process.env.USERPROFILE) + '/.credentials/';
var TOKEN_PATH = TOKEN_DIR + 'sheets.googleapis.com-nodejs-quickstart.json';
// Load client secrets from a local file.
fs.readFile('client_secret.json', function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
// Authorize a client with the loaded credentials, then call the
// Google Sheets API.
authorize(JSON.parse(content), getData);
});
/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
*
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
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);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, function (err, token) {
if (err) {
getNewToken(oauth2Client, callback);
} else {
oauth2Client.credentials = JSON.parse(token);
callback(oauth2Client);
}
});
}
/**
* Get and store new token after prompting for user authorization, and then
* execute the given callback with the authorized OAuth2 client.
*
* @param {google.auth.OAuth2} oauth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback to call with the authorized
* client.
*/
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);
});
});
}
/**
* Store token to disk be used in later program executions.
*
* @param {Object} token The token to store to disk.
*/
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);
}
/**
* Print the names and majors of students in a sample spreadsheet:
* https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
*/
function getData(auth) {
var sheets = google.sheets('v4');
sheets.spreadsheets.values.get({
auth: auth,
spreadsheetId: '<ID REMOVED FOR SAFETY>',
range: 'Sheet1!A1:C',
}, function (err, response) {
if (err) {
console.log('The API returned an error: ' + err);
return;
}
var rows = response.values;
console.log(rows)
if (rows.length == 0) {
console.log('No data found.');
} else {
console.log('Name, Major:');
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
if (row[0] == 'ASR') {
console.log(row)
}
console.log('%s, %s', row[0], row[1]);
}
}
});
}
function writeData() {
var google = require('googleapis');
var sheets = google.sheets('v4');
authorize(function (authClient) {
var request = {
// The ID of the spreadsheet to update.
spreadsheetId: '<ID REMOVED FOR SAFETY>', // TODO: Update placeholder value.
// The A1 notation of the values to update.
range: 'Sheet1!C11', // TODO: Update placeholder value.
// How the input data should be interpreted.
valueInputOption: 'RAW', // TODO: Update placeholder value.
resource: {
// TODO: Add desired properties to the request body. All existing properties
// will be replaced.
},
auth: authClient
};
sheets.spreadsheets.values.update(request, function (err, response) {
if (err) {
console.log(err);
return;
}
// TODO: Change code below to process the `response` object:
console.log(JSON.stringify(response, null, 2));
});
});
function authorize(callback) {
// TODO: Change placeholder below to generate authentication credentials. See
// https://developers.google.com/sheets/quickstart/nodejs#step_3_set_up_the_sample
//
// Authorize using one of the following scopes:
// 'https://www.googleapis.com/auth/drive'
// 'https://www.googleapis.com/auth/spreadsheets'
var authClient = null;
if (authClient == null) {
console.log('authentication failed');
return;
}
callback(authClient);
}
}
Relying heavily upon google's own documentation here: https://developers.google.com/sheets/api/quickstart/nodejs
I cannot however manage to implement spreadsheets.values.update under the function writeData() which I got from https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
My two needs for this code are to be able to read the requested cells and write to specific cells some numbers. I understand that the code may seem hap hazardous but that is simply as I am testing the API before implementing it in a larger project.
Sincere thanks to anyone that would help in finding a solution to sending cell data!
P.S: I have found various other similar APIs floating around on the internet which have confused me in certain attempts, if there is one you might consider to be more suitable please do suggest that one instead!
Thank you again! :)
via Johannes_Kepler
No comments:
Post a Comment