Feature: Worksheet API

[jpillora]

Looking for Contributors Please look through these notes and then the source and if you decide to give this a shot, mention that you're working on this in the comments below and I'll give you push access.

---

A spreadsheet is the entire document, and a worksheet is a tab in the document. Currently this module models 1 spreadsheet and 1 worksheet, it should actually model N worksheets, just as spreadsheets do. The proposed feature would result in the API below. Please +1 below so we can gauge interest. I've specified a rough outline of the changes that need to be made, though they're subject to change.

A few initial notes:

• Adding and deleting spreadsheets is out of scope, see the google-drive module
• The metadata.js will be come worksheet.js since metadata is actually a worksheet API call
• The Metadata class would be become a Worksheet class
• On Spread.load all of the spreadsheets worksheets should be instantiated in spreadsheet.worksheets and if (now optional) worksheetName was set, then it would be set to spreadsheet.worksheet (the current worksheet).
• Important To make this change backwards compatible spreadsheet.send()/receive() actually call spreadsheet.worksheet.send()/receive()
• Finally, there's new APIs to add and remove worksheets
  • spreadsheet.load(title, callback(err, worksheet)) (If it's already in worksheets no request is needed, otherwise it will refresh the worksheet list. On success worksheet becomes the new current spreadsheet.worksheet)
  • spreadsheet.create(title, [numColumns, numRows], callback(err, worksheet)) (Operates as an "upsert" – adds if necessary. On success worksheet becomes the new current spreadsheet.worksheet)
  • spreadsheet.delete(title, callback(err))
  • As these calls are made spreadsheet.worksheets is being kept up to date

See prototype code:

Spreadsheet.load({
  username: creds.username,
  password: creds.password,
  spreadsheetName: 'node-edit-spreadsheet',
  worksheetName: 'Sheet1'  //worksheet becomes optional
}, function run(err, spreadsheet) {
  if(err) throw err;

  spreadsheet.worksheets === [ /* worksheet instances */, /* */ ];
  spreadsheet.worksheet === { /* Sheet1 instance */ };

  //now
  spreadsheet.send();
  spreadsheet.receive();
  //actually does
  spreadsheet.worksheet.send();
  spreadsheet.worksheet.receive();

  //and there's now 'load', 'create' and 'delete' methods

  spreadsheet.load("Sheet 3", function(err, worksheet) {
    //...
  });

  spreadsheet.create("Sheet 2", function(err, worksheet) {
    //...
  });

  spreadsheet.delete("Sheet 1", function(err) {
    //...
  });

});

[Verdier]

+1 !

[Shuyinsama]

+1 I need this so badly :D

[andreconrado]

+1

[adriank]

Hello!

I'm mostly interested in autocreation of worksheet e.g.:

gs.load({
    ...
    worksheetName: "MySheet"
    ...

should silently create MySheet instead of throwing error: "worksheet 'MySheet' not found". It could be configured like that:

gs.load({
    ...
    autoCreateWorkSheets:true,
    worksheetName: "MySheet"
    ...

The simplest thing that would work seems to be just calling G API with:

POST https://spreadsheets.google.com/feeds/worksheets/key/private/full
Content-Type: application/atom+xml

<entry xmlns="http://www.w3.org/2005/Atom"
    xmlns:gs="http://schemas.google.com/spreadsheets/2006">
  <title>Expenses</title>
  <gs:rowCount>1000</gs:rowCount>
  <gs:colCount>20</gs:colCount>
</entry>

https://developers.google.com/google-apps/spreadsheets/#adding_a_worksheet

It would be awesome if you could add this feature to the library, but if you are too busy could you share some hints on how to avoid headaches associated with G API and where to insert this call in the code?

Thanks :)

Adrian

[jpillora]

@adriank I'd opt for the passing a create boolean option. You're right about me being busy :( Haven't used this library in about 2 years and haven't written any Node for about 1 year. Happy to help you get started with this though. In terms of hints, there's not much to add to the notes above. Did you have questions in particular?

[adriank]

@jpillora I also have trouble finding time to code anything recently. Will see if I find someone to add this feature.

BTW. Do you recommend using the library since it's 2 years old? Or is it better to use something else that is supported? Do you know any other library that deals with Google Apps? I'm interested in integrating many Google's APIs and the best solution would be one library that covers all of Google products:).

[jpillora]

@adriank 3,109 downloads in the last month and only 1 reported bug which hardly anyone encounters. This library uses Google Sheets API version 3.0 so as long as that is supported by Google - this library should be fine to use.

[blak3r2]

Hey guys, I got started on adding a new worksheet feature.

I'm encountering an error from google... POST method does not support concurrency i then found this mailing list post... and the guys solution was to remove the If-Match: * on the headers. This unfortunately doesn't work and just results in If-Match or If-None-Match header or entry etag attribute required, I think this worked with protocol version 1. If I changed the protocol version, then the .load method fails to find the spreadsheet so i kinda gave up on that approach.

Any ideas?

Spreadsheet.prototype.addWorksheet = function(worksheetTitle, callback) {
  var url = 'https://spreadsheets.google.com/feeds/worksheets/' + this.spreadsheetId + '/private/full';
  var payload = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">' 
          + '<title>' + worksheetTitle + '</title>'
          + '<gs:rowCount>50</gs:rowCount>'
          + '<gs:colCount>10</gs:colCount>'
          + '</entry>';

  var opts = {
    url: url,
    body: payload,
    method: "POST"
  }

  this.request(opts, function(err, result) {
      if(err) { callback(err); }
      else {
        console.log(result);
      }
  });
}