Tiddlywiki + Google Spreasheets. I want to show you my newer finished work

[Siniy-Kit]

Hi! I make small on-line stores on Tiddlywiki. I was not able to finish my last work, so i make a little demo from it https://heeg.ru/shop2_3.html?id=16jpYS8egNJuLA8HyS-Um6wuwC9A5eFl-IKykIzZB92Y

I think that this demo will be useful for people, who want convert their boring spreadsheets to nice interactive Tiddlywiki page or  very fast static cut site   https://heeg.ru/carimages/static/ 

with checkboxes and images from google drive or any hosting.

[Ste]

Looking realy good!

[Mat]

@Siniy-Kit - I've said it before: Wow! The idea that TW can be the engine for a real commercial store is just amazing! Well done!

How does your system convert the google sheet into tiddlers? Where can we find the code to see how this is done?

As you may know, your solution was the fundamental starting point for Marks and my SheetsIN plugin. Here's the github, the demo with a "special thanks" to you which is also in the plugin, and here's the gg announcement. The SheetsIN plugin is part of the bigger TiddlyGoo project intended to provide solutions for generally connecting TW to the internet, by using google sheets as a "middle step" and a backend. 

...but Google changed their API a few months back so SheetsIN no longer works. So I'm wondering how you solved this? Looking at your demo site, it is very difficult to know what is going on and what tiddlers or plugins that connect to your sheet and extract and process the content - ? Is there a way to see the actual code that does this?

Thank you!

<:-)

[Mat]

By the way, it would of course be better if we have this conversation over at https://talk.tiddlywiki.org/ I note you're also a member there.

<:-)

[Siniy-Kit]

Hi, Mat. I don't know how to write plugins for Tiddlywiki, so I put all my js code to one tiddler https://heeg.ru/shop2_3.html#%24%3A%2Fboot%2Fbootprefix1.js

The part you need begins here

////////Начало нового кода////////

воскресенье, 20 февраля 2022 г. в 11:44:20 UTC+3, Mat:

[Mat]

Ah, great! And this was updated a few months ago to work with Google v4 API, right? Did it take a lot of changing to do the update?

Again thank you.

<:-)

[Mohammad]

Hi @Siniy-Kit

Why not to use Gatha ( Gatha Plugin: Beta Release - Create and Distribute Tiddlywiki Plugins - Plugins - Talk TW)

No need to know Node.JS. Creating a plugin in Gatha is just a piece of cake.

[Siniy-Kit]

Thank, you Mohammad. I'll try it

Mat, as you know, I dont use any google api in my old and New script. I open google spreadsheet direcrly like external javascript file with callback.  This format name is JSONP 

I read you want to make "Google spreadsheet backend"  NOW in spreadsheets scripts we can use GET and POST

so we can save tiddlywiki paswords in hidden spreadsheet and chek it in POST

I hope, you Will finnish your idea with googlespreadsheet plugin for tiddlywiki. 

воскресенье, 20 февраля 2022 г. в 15:44:43 UTC+3, Mohammad:

[Mat]

Siniy-Kit wrote:

Mat, as you know, I dont use any google api in my old and New script. I open google spreadsheet direcrly like external javascript file with callback.  This format name is JSONP 

Aha... @Mark is better suited to comment on this but I would guess then that we used your code and scaled it down to the minimum and built from there so it was no longer JSONP but instead JSON. I can't remember. We'll see if Mark has any thoughts.

 

I read you want to make "Google spreadsheet backend"  NOW in spreadsheets scripts we can use GET and POST

so we can save tiddlywiki paswords in hidden spreadsheet and chek it in POST

Does this, at least theoretically, mean that it would be possible to both fetch data from the spreadsheet and "push" data into it?

 

I hope, you Will finnish your idea with googlespreadsheet plugin for tiddlywiki. 

Yeah... that'd be super cool. If it could be made to work well. And IF it were possible to both fetch and push data between a TW and a goodle sheet then that might even mean it would be a multi-user TW. From any TW. Where you share specific tiddlers. TW could be used for communication, as a plugin store, collaboration, anything.

<:-)

[Siniy-Kit]

JSONP is a very good format. You can get data directly not only from spreadsheets, but for example, you can get all files list from shared google drive folder

https://drive.google.com/drive/folders/1drFCfJcb9KcB2pGHPgXX3W97OSdkVPw_  here is folder

https://script.google.com/macros/s/AKfycbyEhEhAkYKPCrmcyTRg9CG-rpqEIEfEov4voZoxoOY_q8qvJ-049Oke1IlYPRVQL_kSgg/exec?idf=1drFCfJcb9KcB2pGHPgXX3W97OSdkVPw_

here is JSONP of this folder .  in this example I put not JSON, but one line array as argument of callback JavaScript function getImGD  but in spreadsheets we put JSON

the most interesting thing, than spreadsheets understand  mySQL parameters  (WHERE...IN....) in GET so we can download in JSONP not whole spreadsheet, but only lines where column "tags" contain "hi" and so on.  Long ago I make demo in this group of it. And it works without  any API 

We can push data to google spreadsheets in two ways 

1. Old variant - using google form (I make demo in this group)

2. New variant  doPOST() in google script https://stackoverflow.com/questions/43127023/how-do-i-create-a-doposte-function-in-apps-script-project-to-capture-http-post

 

   

воскресенье, 20 февраля 2022 г. в 19:40:55 UTC+3, Mat:

[Mark S.]

On Sunday, February 20, 2022 at 8:40:55 AM UTC-8 Mat wrote:

Siniy-Kit wrote:

Mat, as you know, I dont use any google api in my old and New script. I open google spreadsheet direcrly like external javascript file with callback.  This format name is JSONP 

Aha... @Mark is better suited to comment on this but I would guess then that we used your code and scaled it down to the minimum and built from there so it was no longer JSONP but instead JSON. I can't remember. We'll see if Mark has any thoughts.

Yeah, that's what we're using. I think. Possibly Google hasn't shut it down in all regions, or possibly there is something else wrong with your (Mat's) particular use-case that has nothing to do with the status of JSONP.

[Mat]

Mark S. wrote:

Yeah, that's what we're using. I think. Possibly Google hasn't shut it down in all regions, or possibly there is something else wrong with your (Mat's) particular use-case that has nothing to do with the status of JSONP.

Hmm... @Siniy-kit what do I misunderstand:

1) Your solution is fetching data from a google sheet. 

2) You can only fetch this because the sheet data is presented in json format thanks to a google feature that requires a special URL

3) This special URL *used to* have the format:

https://spreadsheets.google.com/feeds/worksheets/"+specs.id+"/public/values?alt=json-in-script

4) ...but google modified this in august last year so the special URL is different AND the presented data there is json but it is wrapped in some other stuff. Here is the *new* URL format:

https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheet_id + '/values/' + tab_name + '?alt=json&key=' + api_key; 

OR are you using a completely different method to pull the data into your wiki? One of the urls you just posted seems to be a script, so maybe there is a local copy of some magic script in each sheet you share, or maybe there's a way to apply a script to a arbitrary sheets to mangle them... perhaps via a special url (like what you posted... but where is that script stored then?)

@Mark... any ideas? Am I and @Siniy-kit talking past one another? I'm the fool here because I'm dealing with pieces in a puzzle that I don't understand. Is my question wrong?

[Siniy-Kit]

No, JSONP is working all over the world (not only in Russia)

3) Old variant  with alt=json-in-script is not working now, BUT we have two another ways

1) very very very old JSONP (I use it now)

https://spreadsheets.google.com/tq?key=16jpYS8egNJuLA8HyS-Um6wuwC9A5eFl-IKykIzZB92Y&sheet=goods

here we have js function  with JSON argument. google.visualization.Query.setResponse({......})

2) new variant with doGET() in google script

понедельник, 21 февраля 2022 г. в 01:32:58 UTC+3, Mat:

[Mat]

Siniy-Kit wrote:

1) very very very old JSONP (I use it now)

https://spreadsheets.google.com/tq?key=16jpYS8egNJuLA8HyS-Um6wuwC9A5eFl-IKykIzZB92Y&sheet=goods

here we have js function  with JSON argument. google.visualization.Query.setResponse({......})

So... the output in that link is a js function and some more stuff that wraps the JSON. So, do you scrub off the wrapping stuff to accss the clean JSON content or do you use the js function in your own code? ...and is the scrubbing or use of the js function what you have in your tidder under "////////Начало нового кода////////" ?

 

2) new variant with doGET() in google script

This looks very interesting. If I interpret these docs, it seems very direct to both get and post content from and to a sheet (which is one big limitation in the current TiddlyGoo solutions; there is no solution to push data to a sheet from TW). 

But, @Siniy-kit, you currently don't use doGet or doPost at all, right? You solve both push and pull with JSONP, right?

Thank you for your replies.

<:-)

[Siniy-Kit]

Yes, I use  js function  to read external JSONP " in your tidder under "////////Начало нового кода////////"   just open it   in is very simple. It works faster then FETCH

To test it work open https://heeg.ru/shop2_3.html#%24%3A%2FControlPanel  and press "залить данные из гугл таблицы СЕЙЧАС"  spreadsheet must have lists "goods, parts, images, system"   So you have to rewrite my code not to use this lists names. Here are examples, how we can use google spreadsheet id in URL   https://groups.google.com/g/heeg/c/G0eSqbJBvZg/m/9Jr0dtu6CAAJ

2. I dont use  doGET() and  doPOST() in my projects now,  but I plan to use it, because it is new and more interesting instrument of googlespreadsheet 

понедельник, 21 февраля 2022 г. в 22:57:14 UTC+3, Mat: