Google Spreadsheet Import

[danfowler]

From @rgrp on April 5, 2013 19:42

• Create appscript code
  • import function
  • simple UI to provide item
• Instructions for using this in your spreadsheet
• Publish a script which uses the library to the gallery so it can be auto-installed - https://developers.google.com/apps-script/publishing_gallery

Copied from original issue: frictionlessdata/frictionlessdata.io#24

[danfowler]

From @rgrp on April 9, 2013 11:54

Functioning google app script library plus instructions

Demo spreadsheet

• Make a copy (unfortunately you cannot run app scripts if you are not owner!)
• See data packages menu!

[danfowler]

From @rgrp on May 7, 2013 7:52

I'm going to mark as fixed on basis that publishing will take some time (and we probably want to wait to give this some testing).

[danfowler]

From @rgrp on January 23, 2014 9:50

Re-opening as this needs more work and data package spec has updated a little since then.

[danfowler]

From @mattfullerton on March 15, 2014 14:12

A few points:

• The demo spreadsheet appeared to be referring to version 3 of the library; I changed it to 5
• We should add support for multiple CSV files
• We should add support for csv dialects, some of the parameters would just have to be passed through to the CSV reading code
• According to the SDF spec, \r\n is just as acceptable as \n, so it should be checked for in addition to \n
• Where is the best place to put some minimal documentation, i.e. the link to the demo spreadsheet for now
• For an exporter, I was thinking of writing a very small node.js webapp that would export the datapackage.json file using the metadata from the Google Spreadsheets API. Thoughts?

If I were to turn this some of this stuff into issues (and I'm not sure its necessary), where should I put it - new repo, data-protocols or here? As for an exporter, I guess it would merit its own repo.

[danfowler]

From @rgrp on March 16, 2014 15:26

@mattfullerton great points. Here are some thoughts:

• The demo spreadsheet appeared to be referring to version 3 of the library; I changed it to 5

Great

• We should add support for multiple CSV files

Sure though let's start with the simplest thing :-)

• We should add support for csv dialects, some of the parameters would just have to be passed through to the CSV reading code
• According to the SDF spec, \r\n is just as acceptable as \n, so it should be checked for in addition to \n

I feel it could be worth us creating a generic Tabular Data Package API that takes care of doing all the relevant CSV option parsing etc and providing nice standard JSON. This would greatly simplify writing apps like this and avoid repeating the same code in many different locations. In some sense we already have some of this in data.okfn.org webapp (could improve this) - see "Data packages somewhere online" section of #19

• Where is the best place to put some minimal documentation, i.e. the link to the demo spreadsheet for now

I think its time we booted its own repo - as lead on this do you want to create it :-) I suggest naming as: datapackage-to-google-spreadsheet (from can be separate).

• For an exporter, I was thinking of writing a very small node.js webapp that would export the datapackage.json file using the metadata from the Google Spreadsheets API. Thoughts?

Exporter idea makes sense though I think something activated within the google spreadsheet is nice (even if it just calls the nodejs app) - i think for non-expert users this will be simpler (but we can start with the app)

If I were to turn this some of this stuff into issues (and I'm not sure its necessary), where should I put it - new repo, data-protocols or here? As for an exporter, I guess it would merit its own repo.

As above I think we want its own repo for importer (we could also create one for exporter when time comes).

[danfowler]

From @mattfullerton on March 19, 2014 14:19

• We should add support for multiple CSV files

Sure though let's start with the simplest thing :-)

• We should add support for csv dialects, some of the parameters would just have to be passed through to the CSV reading code
• According to the SDF spec, \r\n is just as acceptable as \n, so it should be checked for in addition to \n

I feel it could be worth us creating a generic Tabular Data Package API that takes care of doing all the relevant CSV option parsing etc and providing nice standard JSON. This would greatly simplify writing apps like this and avoid repeating the same code in many different locations. In some sense we already have some of this in data.okfn.org webapp (could improve this) - see "Data packages somewhere online" section of #19

So the key challenge is to ensure that this API is available to any github-hosted data package(?). I'll concentrate on normalised CSV for now then so we can move over to the API later.

• Where is the best place to put some minimal documentation, i.e. the link to the demo spreadsheet for now

I think its time we booted its own repo - as lead on this do you want to create it :-) I suggest naming as: datapackage-to-google-spreadsheet (from can be separate).

Done: https://github.com/okfn/datapackage-to-google-spreadsheet

I'll add the code, some documentation and issues later.

• For an exporter, I was thinking of writing a very small node.js webapp that would export the datapackage.json file using the metadata from the Google Spreadsheets API. Thoughts?

Exporter idea makes sense though I think something activated within the google spreadsheet is nice (even if it just calls the nodejs app) - i think for non-expert users this will be simpler (but we can start with the app)

I agree completely. Also a central API could take over some of the workload of putting the package together, the only GS-specific bit would be pulling out the meta data. Its probably good to wait a little until the exporter/datastore pusher firms up and then we'll know what we're connecting to.

[danfowler]

From @rgrp on March 23, 2014 16:1

All looks good - I imagine first step is commit existing script into https://github.com/okfn/datapackage-to-google-spreadsheet and then go from there.

[danfowler]

From @mattfullerton on March 24, 2014 8:43

Yes, can you give me write access to the repository? I transferred it to OKFN without noticing that I don't have write access to all OKFN repos.

[danfowler]

From @rgrp on March 24, 2014 22:13

@mattfullerton done

[danfowler]

From @mattfullerton on March 26, 2014 6:55

Thanks, everything's been added and documented so we're off to a good start

[danfowler]

From @rgrp on June 7, 2014 12:49

@mattfullerton how is this going? Do you want to update the tools page?

[danfowler]

From @mattfullerton on July 4, 2014 8:44

Finally getting back to you on this. I was on a hiatus for several months due to a new child and a new job. Getting back to it now and have tried to prepare an extensive CSV test data set based on http://dataprotocols.org/json-table-schema/#field-types: https://docs.google.com/spreadsheets/d/1mSHxHi8u176SYPmqGK5RshOgD-nAWvjjQ9REJ_Z43Ks/edit?usp=sharing

The idea is to only support one format per type in line with your API proposal. Let me know what you think. I still have to prepare the JSON schema for it and then start testing and extending the importer accordingly.