Sunday, 9 July 2017

Nodejs access google sheet


Read
you can read google sheet very easily:




Then you can access you sheet by this url:

https://spreadsheets.google.com/feeds/list/change this to your sheet id/od6/public/values?alt=json

your sheet id is here:



By access above url then you can access your whole sheet by this json endpoint.


Write:

Write is more complex:

you need :  https://www.npmjs.com/package/google-spreadsheet

Then you need to create an google app and get the key to access your sheet. You have to do this however, you don't have to link your google app to your sheet, you can create sheet by one google account and create the google app by another account.

To create a google app and get key you need to:

go to : https://console.developers.google.com/project

create a new project or select an existing project.(it doesn't matter to just use an exsiting project because we will only use the api key, if it is a google enginee app, this won't impact your app)











Then it will download a json file automatically:




Open it , you will get your client email (not your gmail , it is a special email) and key:





Then, in your source code:


The spreadsheet key is the long sheet id in your sheet url.



Then, make your sheet open to anybody to write:




Then you just need run the sample code from : https://www.npmjs.com/package/google-spreadsheet


If you just want to add a new row in, then: (it will always append the new row to the end)





    // spreadsheet key is the long id in the sheets URL
    var doc = new GoogleSpreadsheet('xxxx');
    var sheet;

    async.series([
        function setAuth(step) {
            // see notes below for authentication instructions!
            //var creds = require('./google-generated-creds.json');
            // OR, if you cannot save the file locally (like on heroku)
            var creds_json = {
                client_email: 'xxxx@xxx.iam.gserviceaccount.com',
                private_key: '-----BEGIN PRIVATE KEY-----xxxxxxxxx-----END PRIVATE KEY-----\n'
            }

            doc.useServiceAccountAuth(creds_json, step);
        },
        function getInfoAndWorksheets(step) {
            doc.getInfo(function(err, info) {
                console.dir(err);
                console.log('Loaded doc: ' + info.title + ' by ' + info.author.email);
                sheet = info.worksheets[0];
                console.log('sheet 1: ' + sheet.title + ' ' + sheet.rowCount + 'x' + sheet.colCount);
                step();
            });
        },
        function workingWithRows(step) {
            // google provides some query options
            // here sender and feedback are my column headers,
            sheet.addRow({"sender":sender, "feedback":feedback}, function(err, row) {
                if (err) {
                    errorFeedback();
                }else {
                    okCallback();
                }
                console.log(row);
                step();
            });
        }
    ], function(err) {
        if (err) {
            console.log('Error: ' + err);
        }
    });



No comments:

Post a comment