v0.5.1Published 7 years ago

This package has not had recent updates. Please investigate it's current state before committing to using it in your project.


Google Spreadsheets for Meteor

meteor add ongoworks:google-spreadsheets

Option 1

Provides a way to pull a published, public google spreadsheet into a cache collection.


Client: "spreadsheet/fetch","<spreadsheet key>"
3spreadsheetData = GASpreadsheet.findOne({spreadsheet:'<spreadsheet name or number>'})
4if  spreadsheetData
5for index,row of spreadsheetData.cells
6 if ( row[1] ) then value = row[1].value
7 ...

Or you could call on server:

1if ( Meteor.is_server ) {
2    Meteor.startup(function () {
3       Meteor.setInterval(function() {
4'spreadsheet/fetch', key, worksheet, range, rowOneHeader)
5       },50000);
6    });








Used as a cache for results GASpreadsheet

Option 2

Provides a way to push the data from any collection to a Google spreadsheet, which can be either public or private. Then you can also make changes in the spreadsheet and pull them back, overwriting the data in the collection with the data from the spreadsheet.


The collection must have a simple-schema attached using the collection2 package.


The collection must have a simple structure with only top-level fields that are strings, numbers, etc. There is no sub-object or array support yet.

Package Setup

  1. Add the google-spreadsheets package to your Meteor app.
  2. Create a "private" folder at the top level of your Meteor app folder, if you don't already have it.
  3. Go to the Google Developers Console.
  4. Select or create a project for your Meteor app.
  5. Create a service account if you don't already have one for this project: 5. In the sidebar on the left, expand APIs & auth. Select Credentials. 6. Under the OAuth heading, select Create new Client ID. 7. When prompted, select Service Account and click Create Client ID. 8. A dialog box appears. To proceed, click Okay, got it.
  6. Your service account should have a private key associated. Save that private key into a file named "google-key.pem" in your app's "private" folder. You might be given the key within a JSON file, in which case you need to extract and parse it into the separate PEM file (replace "\n" with actual line breaks, etc.).
  7. Make note of the email address created for your service account (a long, random address). You will need this address in later steps.

Spreadsheet Setup

There are only three things you need to do to prep the spreadsheet:

  1. Create a new Google spreadsheet.
  2. Change the name from "Untitled" to something appropriate, like the name of the collection you will import into it. Don't skip this step; a unique name is required.
  3. Share the spreadsheet with the service account email address you created during the "Package Setup" task.

Using the Package in Your App

The package creates two server methods. Currently these do not do all of the work of integrating with your collection. Instead, you can make your own server methods to do that. Here are some example methods you could create in your app:

1pullAllSteps: function () {
2  var spreadsheetName = 'Steps'; // must match exactly the name you gave your Google spreadsheet
3  var serviceEmail = ''; // this is fake; replace with your own
5  var result ="spreadsheet/fetch2", spreadsheetName, "1", {email: serviceEmail});
7  // Remove all existing
8  Steps.remove({});
10  // Gather property names
11  var propNames = {};
12  _.each(result.rows, function (rowCells, rowNum) {
13    var doc = {};
14    _.each(rowCells, function (val, colNum) {
15      if (+rowNum === 1) {
16        propNames[colNum] = val;
17      } else {
18        var propName = propNames[colNum];
19        if (propName) {
20          doc[propName] = val;
21        }
22      }
23    });
24    if (+rowNum > 1) {
25      Steps.insert(doc);
26    }
27  });
29writeAllSteps: function () {
30  var spreadsheetName = 'Steps'; // must match exactly the name you gave your Google spreadsheet
31  var serviceEmail = ''; // this is fake; replace with your own
33  var obj = {};
34  obj[1] = {}
35  var colPropNames = {};
36  var col = 1;
37  _.each(Steps.simpleSchema().schema(), function (def, key) {
38    obj[1][col] = key;
39    colPropNames[key] = col;
40    col++;
41  });
43  var row = 2;
44  Steps.find().forEach(function (step) {
45    obj[row] = {};
46    _.each(step, function (val, prop) {
47      var pCol = colPropNames[prop];
48      if (!pCol)
49        return;
50      obj[row][pCol] = val.toString();
51    });
52    row++;
53  });
55"spreadsheet/update", spreadsheetName, "1", obj, {email: serviceEmail});

TODO - the above methods could be pulled into the package methods, just passing in the collection object.

So if you are building an app that needs to pull or push content from any unknown spreadsheets that are owned by any random users, you simply need to ask the user what the name of the spreadsheet is, and then tell the user to share that spreadsheet with your service account email address. (XXX not sure about potential name contention?)