samcday / node-google-spreadsheets Goto Github PK

The URL doesn't seem to have the worksheet ID, so I'm forced to get all worksheets and filter by title. Any options there?

Hi again!
I've been playing around with reducing the size of the browser build. When using browser-request and statuses, the size drops from ~1MB to ~40kB.

Building with browser-request is as simple as adding browser: { 'request': 'browser-request'} to package.json. Using statuses will replace http.STATUS_CODES[code] with statuses[code], and the extra "burden" of another dependency. What do you think of a PR?

At the same time, I found out that this commit broke the browser build (line 35 in spreadsheets.js). Setting headers in browser will force an "OPTIONS" request. Which fails, as google does not set Access-Control-Allow-Origin on OPTIONS requests.

how should one specify certain columns to return in the results from get rows?

$ cat gsheet.js
#!/usr/bin/env node

var GoogleSpreadsheets = require("google-spreadsheets");

var key = process.argv[2];
var range = process.argv[3];

GoogleSpreadsheets.cells(
      {
        key: key,
        worksheet: 0,
        range: range
      },
      function(err, cells) {
        console.log("cells:", err, cells);
      }
);
$ gsheet.js 1Bb-SLy4E-alUUSJ68Qb5nNmZG6HsPbG2j6Ye_QZO77Y A1:A3
/Users/ryan/node_modules/google-spreadsheets/lib/spreadsheets.js:258
    throw new Error("Worksheet not specified.");
    ^

Error: Worksheet not specified.
    at Function.Spreadsheets.cells (/Users/ryan/node_modules/google-spreadsheets/lib/spreadsheets.js:258:11)
    at Object.<anonymous> (/Users/ryan/s/js-helpers/gsheet.js:8:20)
    at Module._compile (module.js:434:26)
    at Object.Module._extensions..js (module.js:452:10)
    at Module.load (module.js:355:32)
    at Function.Module._load (module.js:310:12)
    at Function.Module.runMain (module.js:475:10)
    at startup (node.js:117:18)
    at node.js:951:3

Trying to load range A1:A3 from this sheet.

I'm getting an error whilst using the API, it seems as the returned data doesn't have data.title set and this is undefined.

Note: The key of the spreadsheet is correct, so it works when I replace /d//edit/ in the google spreadsheet URL. The spreadsheet is available via link sharing access.

What else do I need to do in order to work with this library?
I tried out username/password as well, but still no success.

My Code:

var config       = require('./config');
var Spreadsheets = require('google-spreadsheets');

var sheet = new Spreadsheets({
    key: config.google.sheet
}, function(err, spreadsheet) {

    console.log(spreadsheet.worksheets[0]);

});

Stacktrace:

    /home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/lib/spreadsheets.js:165
        this.title = data.title.$t;
                     ^
    TypeError: Cannot read property 'title' of undefined
        at new Spreadsheet (/home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/lib/spreadsheets.js:165:19)
        at /home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/lib/spreadsheets.js:73:12
        at Request._callback (/home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/lib/spreadsheets.js:56:3)
        at Request.self.callback (/home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/node_modules/request/request.js:372:22)
        at Request.emit (events.js:110:17)
        at Request.<anonymous> (/home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/node_modules/request/request.js:1317:14)
        at Request.emit (events.js:129:20)
        at IncomingMessage.<anonymous> (/home/christoph/Software/_stuff_/backend/node_modules/google-spreadsheets/node_modules/request/request.js:1265:12)
        at IncomingMessage.emit (events.js:129:20)
        at _stream_readable.js:907:16

When I run spreadsheets.js > getFeed I get a 400 response. Is this because the API's have changed?

I've tried 2 of the examples so far, both come back with errors. Normal looking spreadsheet http://cl.ly/image/1n0g420f3q1N

My code

GoogleSpreadsheets({
            key: "MYKEY"
        }, function(err, spreadsheet) {
            spreadsheet.worksheets[0].cells({
                range: "A1R1:A1CR2"
            }, function(err, result) {
                console.log(result);
            });
        });

Which gives me

spreadsheet.worksheets[0].cells({
                                       ^
TypeError: Cannot read property 'worksheets' of null

And if I try one of the direct methods I get

node_modules/google-spreadsheets/lib/spreadsheets.js:238
    if(typeof data.entry !== "undefined" && data.entry !== null) {
                  ^
TypeError: Cannot read property 'entry' of undefined

And yes I've my spreadsheet public for unauthed testing. Thoughts?

When trying to use node-google-spreadsheets on a newly created spreadsheet, I get

TypeError: Cannot read property 'feed' of undefined
    at Request._callback ([...]/node_modules/google-spreadsheets/lib/spreadsheets.js:56:16)
    at Request.self.callback ([...]/node_modules/google-spreadsheets/node_modules/request/request.js:372:22)
    at Request.emit (events.js:98:17)
    at Request.<anonymous> ([...]/node_modules/google-spreadsheets/node_modules/request/request.js:1317:14)
    at Request.emit (events.js:117:20)
    at IncomingMessage.<anonymous> ([...]/node_modules/google-spreadsheets/node_modules/request/request.js:1265:12)
    at IncomingMessage.emit (events.js:117:20)
    at _stream_readable.js:929:16
    at process._tickCallback (node.js:419:13)

While when using it on an old spreadsheet, it works.

Copying the old spreadsheet and replacing its content manually instead of creating a new spreadsheet seems to make it work, too. But now Google adds a warning on top of the old-format spreadsheets saying "Your older spreadsheets will soon be upgraded to the new version of Google Sheets". So we can't rely on this workaround, of course...

I have a test spreadsheet here. It's set to public link view shared.

when I run this simple code:

var GoogleSpreadsheets = require("google-spreadsheets");

GoogleSpreadsheets({
    key: '15XaE_P3WS_1fNahoJtrZfXKV3E2LPeGdoY2syspdm9M'
}, function(err, spreadsheet) {
    if (err) {
        console.log(err);
        process.exit(1);
        return;
    }

    spreadsheet.worksheets[0].cells({
        range: "A2:C"
    }, function(err, cells) {
        if (err) {
            console.log(err);
            process.exit(1);
            return;
        }
        console.log(cells);
    });
});

I get this error:

TypeError: Cannot read property 'title' of undefined
    at new Spreadsheet (/Users/konsumer/Desktop/test/node_modules/google-spreadsheets/lib/spreadsheets.js:165:19)
    at /Users/konsumer/Desktop/test/node_modules/google-spreadsheets/lib/spreadsheets.js:73:12
    at Request._callback (/Users/konsumer/Desktop/test/node_modules/google-spreadsheets/lib/spreadsheets.js:56:3)
    at Request.self.callback (/Users/konsumer/Desktop/test/node_modules/google-spreadsheets/node_modules/request/request.js:121:22)
    at Request.EventEmitter.emit (events.js:98:17)
    at Request.<anonymous> (/Users/konsumer/Desktop/test/node_modules/google-spreadsheets/node_modules/request/request.js:985:14)
    at Request.EventEmitter.emit (events.js:117:20)
    at IncomingMessage.<anonymous> (/Users/konsumer/Desktop/test/node_modules/google-spreadsheets/node_modules/request/request.js:936:12)
    at IncomingMessage.EventEmitter.emit (events.js:117:20)
    at _stream_readable.js:919:16

Am I doing it wrong, or does this library not work with google drive, anymore?

Trying to access a Google Team Drive spreadsheet leads to a TypeError: Cannot read property '$t' of undefined bug because the author object is empty.

Using this in https://github.com/reactioncommerce/meteor-google-spreadsheets and would love to send a PR to fix!

I think you should remove xml parser and let google send json data feeds
?alt=json

thank you

All my keys have their casing removed. I get keys like useonlywronganswerpool instead of useOnlyWrongAnswerPool. This is important because once these go to my database, i can't trust the column headers to match my expected field types.

Does google do this or is this a bug somewhere?

Thanks!

Hi, First of all Thanks for sharing this module. I am currently writing and Nodejs/Angular application to read data from spreadsheet and display data to user. In the process I am using this module to read google spreadsheet using OAUTH2. I am having CORS issue on return from REDIRECT URL (callback url).
XMLHttpRequest cannot load https://accounts.google.com/o/oauth2/auth?access_type=offline&scope=https%3…t.com&redirect_uri=http%3A%2F%2Flocalhost%3A1337%2Ferrors%2Foauth2callback. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:1337' is therefore not allowed access.

All I am doing is like below: Here is how my node module looks like:
var scopes = [
"https://spreadsheets.google.com/feeds/"
];
var authUrl = oauth2Client.generateAuthUrl({
access_type: 'online', // 'online' (default) or 'offline' (gets refresh_token)
scope: scopes // If you only need one scope you can pass it as string
});
router.get('/', function (req, res) {
console.log('Redirecting to URL: ' + JSON.stringify(authUrl));
res.redirect(authUrl);
});
router.get('/oauth2callback', function(req, res) {
console.log('Got code: ', (req.query.code));
oauth2Client.getToken(req.query.code, function (err, tokens) {
// Now tokens contains an access_token and an optional refresh_token. Save them.
if (!err) {
oauth2Client.setCredentials(tokens);
// Assuming you already obtained an OAuth2 token that has access to the correct scopes somehow...
GoogleSpreadsheet({
key: '1vj5LdGYvxIjm7HIBFi1V44cqddd36Pxz0bu9xv1YvtysVg',
auth: oauth2Client
}, function(eror, spreadsheet) {
var worksheet = spreadsheet.worksheets[0];
console.log('* worksheets - ', worksheet);
worksheet.rows({
key: '1vj5LdGYvxIjm7HIBFi1V44cqddd36Pxz0bu9xv1YvtysVg',
worksheet: worksheet.id
},function(error,rows) {
// console.log('* rows - ', rows);
res.send({
length:rows.length,
rows:rows
});

            });
        });
    } else {
        res.sendStatus(500);
    }
});
res.sendStatus(500);

});

From my Angular JS controller, I am calling above get method and facing CORS issue. Can you let me know what the issue is?

$ cat gsheet.js
#!/usr/bin/env node

var GoogleSpreadsheets = require("google-spreadsheets");

var key = process.argv[2];
var range = process.argv[3];

GoogleSpreadsheets.cells(
      {
        key: key,
        worksheet: "Sheet1",
        range: range
      },
      function(err, cells) {
        console.log("cells:", err, cells);
      }
);
$ gsheet.js 1Bb-SLy4E-alUUSJ68Qb5nNmZG6HsPbG2j6Ye_QZO77Y A1:A3
/Users/ryan/node_modules/google-spreadsheets/lib/spreadsheets.js:284
    if(typeof data.entry !== "undefined" && data.entry !== null) {
                  ^

TypeError: Cannot read property 'entry' of undefined
    at /Users/ryan/node_modules/google-spreadsheets/lib/spreadsheets.js:284:19
    at Request.reqCallback [as _callback] (/Users/ryan/node_modules/google-spreadsheets/lib/spreadsheets.js:70:5)
    at Request.self.callback (/Users/ryan/node_modules/google-spreadsheets/node_modules/request/request.js:198:22)
    at emitTwo (events.js:87:13)
    at Request.emit (events.js:172:7)
    at Request.<anonymous> (/Users/ryan/node_modules/google-spreadsheets/node_modules/request/request.js:1063:14)
    at emitOne (events.js:82:20)
    at Request.emit (events.js:169:7)
    at IncomingMessage.<anonymous> (/Users/ryan/node_modules/google-spreadsheets/node_modules/request/request.js:1009:12)
    at emitNone (events.js:72:20)

Trying to load range A1:A3 from this sheet.

As explained here

On line 45 of spreadsheet.js,

use

var parser = new xml2js.Parser(xml2js.defaults["0.1"]);

instead of

var parser = new xml2js.Parser();

I'm not sure if this is a limitation of Google's API, or if this is specifically implemented in this package, but fetching a spreadsheet doesn't include empty cells.

So given a spreadsheet with an empty cell in the second row:

You get this response (headings removed):

{
    // Headings removed

    '1': {
        row: '2',
        col: '1',
        value: 'Cell 1'
    },
    '3': {
        row: '2',
        col: '3',
        value: 'Cell 3'
    }
}

Normally this wouldn't be an issue, but a project I'm working on has a requirement to report on empty cells. Is there a way around this I'm not aware of?

$ cat gsheet.js
#!/usr/bin/env node

var GoogleSpreadsheets = require("google-spreadsheets");

var key = process.argv[2];

console.log("sheet:", key);

GoogleSpreadsheets(
      {
        key: key
      },
      function(err, sheet) {
        console.log(err, sheet);
      }
);
$ gsheet.js 1Bb-SLy4E-alUUSJ68Qb5nNmZG6HsPbG2j6Ye_QZO77Y
sheet: 1Bb-SLy4E-alUUSJ68Qb5nNmZG6HsPbG2j6Ye_QZO77Y
[TypeError: Cannot read property 'title' of undefined] null

Trying to fetch this sheet.

When I ask for a row, I get some odd things. Namely 1) and ID, 2) a "content" object and 3) empty things are $t

{ id: { '$t': 'https://spreadsheets.google.com/feeds/list/1TQZjoAF15aSnXwZlQ_l71H9WqwCJBPDEy6i1tDUa2UY/o3l1e6y/public/values/cpzh4' },
  updated: '2015-09-16T19:42:40.798Z',
  title: { '$t': '' },
  content: 'question: Name this president., answer: George Washington, clarification: 1st President, 1789-1797, imageurl: https://dl.dropboxusercontent.com/s/et8jay2by28taq7/geowash.jpg?dl=0',
  types: { '$t': '' },

The expected output would be

{
    updated: '2015-09-16T19:42:40.798Z',
    title: '',
    types: ''
}

Any idea why all that extra fluff is added?

In spreadsheets.js we see a check for the googleapis dependency version, explicitly checking for a 1.x series version.

The 2.x series of googleapis has been available since March. I've used:

"google-spreadsheets": "0.4.2",
"googleapis": "2.0.4",

together and AFAICT it works. What do you think about updating the check to support 2.x series googleapis?

Hi, I get this warning as the module is checking for googleapis versions 1.X and 2.X

Link to Code

The latest published version right now has a dependency on version 20.1.0 googleapis": "^20.1.0" so even the tests spit this warning. Am I missing something. Right now I'm using v27.0.0 of the googleapis and tests are passing. Can you update this? Should I do a PR?

Thanks.

Google recently communicated that Documents List API will be discontinued on April 20, 2015. This change means that service calls for this API will no longer be supported, and any Google Apps features that are implemented using this API will no longer function.

One of the URL's that will be discontinued is https://spreadsheets.google.com/feeds/ which is used by this module. It's suggested to migrate to the Drive API.

To summarize, node-google-spreadsheets will no longer function as of April 20, 2015 if no action is taken.

In my particular use case, I'm looking to only make changes to my own google sheet. Is there a way I can implement this without creating a callback URL and just using the "server key" that is generated via the google APIs?