[TW5] Using jQuery to fetch JSON (tiddlers) directly from Google Sheets?

[Hegart Dmishiv]

Hi all,

Professor Schneider at {{DesignWrite}} has recently shared a link to this blog post, which suggests it is possible to access the information in a Google Sheet as a JSON file, by using jQuery. This looks very promising for my semester project, but I have no idea how to implement it, as I am a total n00b at Javascript.

What I was thinking for this, is perhaps synchronising a set of tiddlers of type JSON data from my Google Sheets. Many of my worksheets in Google are already formatted in such a way that they will generate TiddlyWiki tiddlers, with just the relevant column headings as expected by TiddlyWiki, and rows for each tiddler. I currently export these manually to CSV, then convert them to JSON. This new idea would save on those extra steps. Once I have them as JSON data tiddlers full of JSON tiddlers, I should then be able to bulk import the tiddlers into my wiki space from the JSON data tiddlers fairly easily.

This solution would be used initially to replace my cumbersome procedure for working with the SampleData which I'm generating for testing the TiddlyCRM project. This sample data gets imported and purged often. However, it may also have application later for the live data, if the end user wants to bulk-import their legacy information into TiddlyCRM.

Any advice on how to implement this would be greatly appreciated.

Kind regards,

Hegart.


PS. If you're looking at my Google Sheets data, it would be useful to know that all the worksheets to the left of the Random Source worksheet are formatted for importing into TiddlyWiki. Everything else is just hidden workings and shouldn't appear within a TiddllyWiki wiki at all.

[Jeremy Ruston]

Hi Hegart

Professor Schneider at {{DesignWrite}} has recently shared a link to this blog post, which suggests it is possible to access the information in a Google Sheet as a JSON file, by using jQuery. This looks very promising for my semester project, but I have no idea how to implement it, as I am a total n00b at Javascript.

Interesting. I checked it out, creating a simple public spreadsheet. The sharing works as advertised, except that the JSON format used is pretty complex, but parseable.

My sample spreadsheet looks like this:

The resulting JSON is attached below. Weirdly, it doesn’t even place the cells of the spreadsheet in separate entries; they’re merged together with colons.

Anyhow, I’m sure with a bit of research one could figure out the format and get something useful out of it, but it feels a bit Google-specific compared to the general utility of extending TW with the CSV import facilities you need,

Best wishes

Jeremy.

{

  "version": "1.0",

  "encoding": "UTF-8",

  "feed": {

    "xmlns": "http:\/\/www.w3.org\/2005\/Atom",

    "xmlns$openSearch": "http:\/\/a9.com\/-\/spec\/opensearchrss\/1.0\/",

    "xmlns$gsx": "http:\/\/schemas.google.com\/spreadsheets\/2006\/extended",

    "id": {

      "$t": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic"

    },

    "updated": {

      "$t": "2016-04-03T19:02:55.541Z"

    },

    "category": [

      {

        "scheme": "http:\/\/schemas.google.com\/spreadsheets\/2006",

        "term": "http:\/\/schemas.google.com\/spreadsheets\/2006#list"

      }

    ],

    "title": {

      "type": "text",

      "$t": "Sheet1"

    },

    "link": [

      {

        "rel": "alternate",

        "type": "application\/atom+xml",

        "href": "https:\/\/docs.google.com\/spreadsheets\/d\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/pubhtml"

      },

      {

        "rel": "http:\/\/schemas.google.com\/g\/2005#feed",

        "type": "application\/atom+xml",

        "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic"

      },

      {

        "rel": "http:\/\/schemas.google.com\/g\/2005#post",

        "type": "application\/atom+xml",

        "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic"

      },

      {

        "rel": "self",

        "type": "application\/atom+xml",

        "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic?alt=json"

      }

    ],

    "author": [

      {

        "name": {

          "$t": "jeremy.ruston"

        },

        "email": {

          "$t": "jeremy...@gmail.com"

        }

      }

    ],

    "openSearch$totalResults": {

      "$t": "3"

    },

    "openSearch$startIndex": {

      "$t": "1"

    },

    "entry": [

      {

        "id": {

          "$t": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cokwr"

        },

        "updated": {

          "$t": "2016-04-03T19:02:55.541Z"

        },

        "category": [

          {

            "scheme": "http:\/\/schemas.google.com\/spreadsheets\/2006",

            "term": "http:\/\/schemas.google.com\/spreadsheets\/2006#list"

          }

        ],

        "title": {

          "type": "text",

          "$t": "Once I"

        },

        "content": {

          "type": "text",

          "$t": "two: caught a , three: fish, four: alive"

        },

        "link": [

          {

            "rel": "self",

            "type": "application\/atom+xml",

            "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cokwr"

          }

        ]

      },

      {

        "id": {

          "$t": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cpzh4"

        },

        "updated": {

          "$t": "2016-04-03T19:02:55.541Z"

        },

        "category": [

          {

            "scheme": "http:\/\/schemas.google.com\/spreadsheets\/2006",

            "term": "http:\/\/schemas.google.com\/spreadsheets\/2006#list"

          }

        ],

        "title": {

          "type": "text",

          "$t": "Six"

        },

        "content": {

          "type": "text",

          "$t": "two: Seven, three: Eight, four: Nine, five: Ten"

        },

        "link": [

          {

            "rel": "self",

            "type": "application\/atom+xml",

            "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cpzh4"

          }

        ]

      },

      {

        "id": {

          "$t": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cre1l"

        },

        "updated": {

          "$t": "2016-04-03T19:02:55.541Z"

        },

        "category": [

          {

            "scheme": "http:\/\/schemas.google.com\/spreadsheets\/2006",

            "term": "http:\/\/schemas.google.com\/spreadsheets\/2006#list"

          }

        ],

        "title": {

          "type": "text",

          "$t": "Then I"

        },

        "content": {

          "type": "text",

          "$t": "two: put it, three: in, four: again"

        },

        "link": [

          {

            "rel": "self",

            "type": "application\/atom+xml",

            "href": "https:\/\/spreadsheets.google.com\/feeds\/list\/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4\/od6\/public\/basic\/cre1l"

          }

        ]

      }

    ]

  }

}

What I was thinking for this, is perhaps synchronising a set of tiddlers of type JSON data from my Google Sheets. Many of my worksheets in Google are already formatted in such a way that they will generate TiddlyWiki tiddlers, with just the relevant column headings as expected by TiddlyWiki, and rows for each tiddler. I currently export these manually to CSV, then convert them to JSON. This new idea would save on those extra steps. Once I have them as JSON data tiddlers full of JSON tiddlers, I should then be able to bulk import the tiddlers into my wiki space from the JSON data tiddlers fairly easily.

This solution would be used initially to replace my cumbersome procedure for working with the SampleData which I'm generating for testing the TiddlyCRM project. This sample data gets imported and purged often. However, it may also have application later for the live data, if the end user wants to bulk-import their legacy information into TiddlyCRM.

Any advice on how to implement this would be greatly appreciated.

Kind regards,

Hegart.

--
You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.
To post to this group, send email to tiddl...@googlegroups.com.
Visit this group at https://groups.google.com/group/tiddlywiki.
To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/fe13e647-fc37-4e68-8ec4-9be2ddc11353%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hegart Dmishiv]

Hi Jeremy,

Thanks for looking into this. I have no idea where to even begin with trying to parse that JSON code. It reminds me of something that Mat and Jed were doing a while ago for the Twederation / Tiddlyverse. Maybe this will have some use to them. Maybe, if they find it useful and write a parser for it, I could use the same in my semester project. Anyway, I think for now this is way beyond my mere skills, and I'll stick to what is working for me for now, even if it is a bit clunky.

Hegart.

[sini-Kit]

we can get any data from the google table cell if we know its address in format R21C454

for example here is my table https://docs.google.com/spreadsheets/d/1tkOhPUj7KpdrTNHu3Zd1qPfNrGgyp1SLDRi7-8iBlQ0/

we can get R131C8 by this code http://jsfiddle.net/nnd4L1pw/1/

if we know how many rows and colons we have, we can import all table.

if somebody already made macros for  spreadsheets ?

[Martian]

This is GData - Google Data ATOM/RSS represented in JSON format.

So originally it is in XML format. 

If you try in browser https://spreadsheets.google.com/feeds/list/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4/01/public/basic in Chrome or FF then browser will get and recognize ATOM xml feed and ask you in which program to subscribe to this feed.

As described in example of retrieving JSON list|cells define data in each entry array element. 

If entry element per sheet cell is needed then just get https://spreadsheets.google.com/feeds/cells/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4/01/public/basic?alt=json

P.S.

Also I've found that at leat 3 different endings define a structure of content elements  - they are 'basic', 'full', 'values'

https://spreadsheets.google.com/feeds/cells/1Uxon1ZBYVxTVlw5AhEFY8b6SMlrG_wAHt1mf1pXtEd4/01/public/basic?alt=json

воскресенье, 3 апреля 2016 г., 22:15:15 UTC+3 пользователь Jeremy Ruston написал:

[Devin Weaver]

On Sunday, April 3, 2016 at 2:44:53 PM UTC-4, Hegart Dmishiv wrote:

Professor Schneider at {{DesignWrite}} has recently shared a link to this blog post, which suggests it is possible to access the information in a Google Sheet as a JSON file, by using jQuery. This looks very promising for my semester project, but I have no idea how to implement it, as I am a total n00b at Javascript.

Using JSON in TiddlyWiki is quite simple. There is a utility function for doing just that. $tw.utils.httpRequest I even have an example on how to turn it into a Promise (for modern browsers).

However, the output of the google sheets seems ill equipped for a simple data import. Seems you would be better off exporting to a saner format like CSV and import that file. The JSON from google sheets would be more useful for say a plugin or widget that depends on the live network connection to display parsed results.

[sini-Kit]

My idea was to save all new tiddlers TW5 to spreadsheets  and load them from spreadsheets  when wiki start. And I want to do it without API, not to use server code and keys.

I know how to save tiddler to google sheets in tw5, but i don't know how to load them in correct way:) 

суббота, 23 апреля 2016 г., 0:31:28 UTC+3 пользователь Devin Weaver написал:

[Jed Carty]

I did a bit with this for twederation. I have a widget that will submit data to a google form and stored in google sheets. Going the other way is a pain but can be done.
I need to find where it is but I was working on a widget that would load data from google sheets. It was right before I started my current jobs so I don't know if I said anything about it. I will have to figure out where it is. I only have three more weeks of working like this so I am hoping to finish this and a bunch of other small projects I have been thinking about for the past few months once my current contract is up.

[Birthe C]

Hi Jed,

I think you mentioned something here: https://groups.google.com/d/msg/tiddlywiki/_v4CYU5Hx-Q/gGP3_9fBGAAJ

Birthe

[sini-Kit]

I have found an interesting bug. if we use /list/ and we have empty line in our table it is not possible to get data under this empty line. But with /cells/ we can see all table with empty rows.

https://jsfiddle.net/t77okvbw/

[Jed Carty]

Birthe and sini-Kit,

Thank you both! I am terrible at keeping track of my things.

I have some time today so I have been working on this a bit. I have a widget to submit data and a widget to fetch data. It doesn't do anything with the fetched data yet and it just uses a static url so it isn't anywhere near finished but I got it to use web workers so it does asynchronous requests. I need to figure out how to make it parse the output and figure out how to get the sheets api to give me data in a nice form.

sini-Kit,

Do you have somewhere with good documentation for the google sheets API? Everything I have found I got off of examples I found on other sites.

We should be able to extend what I have (maybe combined with the startup actions plugin I made a while back) to get spreadsheet data when the wiki loads and then use templates to build whatever display you would like to use for the data.

It doesn't do anything interesting yet, but the sending and fetching widgets are here: http://ooktech.com/jed/ExampleWikis/TWederation/WebWorkerTest/
Clicking on the button that says 'Test' will fetch the data from a google sheet.

The tiddlers 
$:/plugins/inmysocks/TWederation/action-requestdata.js
$:/plugins/inmysocks/TWederation/action-submitform.js
$:/plugins/inmysocks/TWederation/XMLHttpRequestWorker
are what are used for it. I need to put together a better demo but if anyone wants to look at it I think that a more generic worker setup could be useful for other applications.

[sini-Kit]

Hi! Test button don't work (I see  only something in console) . I try in IE and Chrome. google sheets API use keys. So all the examples on .NET and Java (server need). But  we can add information to table without keys using google forms and connecting them to google sheets, but we cant delete or modify old, and BUT we can add new version of tiddler to google sheets without deleting old, and render in TW5 only last variant of tiddler. Our table will be great, it will be nice to have way to delete old variants...

воскресенье, 24 апреля 2016 г., 14:17:26 UTC+3 пользователь Jed Carty написал:

[Jed Carty]

That is all the test button does right now. The interesting things I have done are more on the back end where it uses web workers so it doesn't hurt the performance of the wiki when you are loading things from slow sites. Since things are mostly hidden from normal use now I posted in the dev group and will probably not post here until I have something more interesting to show for it.

[sini-Kit]

we can use Api in spreadsheet https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet

For example if we put this code to scripts it will send e-mail with last row....

 

function sendMail() {
   //test_mail-название листа в таблице
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ответы на форму (1)');
   var values = sheet.getDataRange().getValues();
 
   // проверяем наличие хотя бы одной записи
  if (values.length < 2)
     return;
 
   var lastValueIndex = values.length - 1;
   
   var myEmail = 'in...@info.ru'; // Ваш e-mail
   var subject = 'НОВЫЙ ЗАКАЗ!!!';
 
   
   //values[lastValueIndex][1]-это столбик из таблицы, нужно будет менять циферку. Так же проверил, можно ли несколько в одно объяденить, можно
  var mes=values[lastValueIndex][6]+("\n")+values[lastValueIndex][2]+("\n")+values[lastValueIndex][3]+("\n")+values[lastValueIndex][4]+("\n")+values[lastValueIndex][5]+("\n")+values[lastValueIndex][7];
   
   
   
   // отправляем письмо
  MailApp.sendEmail(myEmail, subject,mes);
 
 }

using api we can delete old  tiddlers with the same title from google table (duplicates appear when we edit tiddler) .

the next idea is to add field with password to our google form, and verify it only on google forms side and remove cell this password from google table after tiddler adding. 

so we can protect our form :)

so I see the way to save practically all wiki content to Spreadsheet, without using server code... 

понедельник, 25 апреля 2016 г., 9:31:50 UTC+3 пользователь Jed Carty написал:

[Martian]

Hi Jed!

There is a description how to add and to update rows in Google Sheets with simple POST requests https://developers.google.com/google-apps/spreadsheets/data#add_a_list_row

But before doing this some Auth process should be done, so it could be a little tricky. 

But this is definitely a way to store tiddlers as Google Sheet rows.

P.S. Also interesting feature - it's possible to request Query

for example

GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=age>25%20and%20height<175

where age and height are just headers from the sheet - values from the first row.

or like this

GET https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full?min-row=2&min-col=4&max-col=4

[Jeremy Ruston]

Hi Sini-Kit

Hi! Test button don't work. I try in IE and Chrome. google sheets API use keys. So all the examples on .NET and Java (server need). But  we can add information to table without keys using google forms and connecting them to google sheets, but we cant delete or modify old, and BUT we can add new version of tiddler to google sheets without deleting old, and render in TW5 only last variant of tiddler. Our table will be great, it will be nice to have way to delete old variants…

Just to add that I think this approach of using Google Sheets for tiddler storage is a terrific idea, and would love to see this happen.

Best wishes

Jeremy

[sini-Kit]

 Hi! I continue my experiments. I make google form with password field, this form puts its fields to first hidden table. And... I make second PUBLIC google table, which take all fields from hidden table except password colon.  So my password is hidden in goodle form (and nobody can get it) . Here is the demo of my experiment  https://jsfiddle.net/t77okvbw/5/  it is static page, but you can put data to it if you know password.

you can test it password is admin

we can use it as comments system  to our tiddlers.

If anybody know how to get google table not using jQuery ?

[Mat]

Here are some bookmarks on using Google Sheets as backend db for TW.

I briefly looked into this matter as I was creating the commenting system for TWaddle (which you can see there) that uses an iframe with a google form. It has the finesse to fetch the name of the current tiddler automatically and supply it along in the form. Other tiddler data can be fetched as well. I believe we reused the technique in the TWederation experiments but I don't recall if it was dismissed or if Jed improved it along the way.

<:-)

[Jed Carty]

You can parse the json object returned by the xmlhttprequest in my example. It isn't as simple but it is possible.

[DesignWriteX SteveSchneider]

Hey folks, this all looks very intriguing, and I hope to work with it in the next week or two. What it makes possible is, among other things, allowing individuals to write tiddlers from google forms, and allowing all many ifttt.com channels to write tiddlers by appending rows to google spreadsheets (thus allowing one to "tweet into their tiddlywiki") among other things. Thanks for this work; I look forward to exploring it, and will post some examples here once I've built them... //steve.

--

DesignWriteX is the administrative account for {{DesignWrite}}, the Designing and Writing Interactive Texts learning community.

http://bit.do/designwrite

Coordinator: Steve Schneider, st...@sunyit.edu

[Alex Hough]

hi Steve,

Writing for IFTT could open up the possibility of writing tiddlers from email alerts from google scholar.?

Alex

--

You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.
To post to this group, send email to tiddl...@googlegroups.com.
Visit this group at https://groups.google.com/group/tiddlywiki.

To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/CALqRa%2BJkhxxoMRYqFUEfYgN6AW2Obgtm6RuxPauWeQE8Oqk2Zw%40mail.gmail.com.