I have a JSON url, how do I make the tm-http-request?

#1

I’m studying the new tm-http-request and the Zotero example. My hope is to do the same thing but for Google Sheets, i.e import sheet rows to become tiddlers.

The Zotero example fetches the data from this JSON page.

I instead have generated this Sheets JSON page (annoyingly, to view it you need to be in logged into a google account despite that I set all the knobs to sharing as publicly as possible) …but this is what it looks like:

{"data":[{"id":"2021-02-26T20:31:24.598Z","title":"QuineTest","text":"What hath God wrought?"},{"id":"2021-02-26T20:15:04.764Z","title":"Bindweed","text":"<center>\n[img[http://www.aphotoflora.com/images/convolvulaceae/calystegia_silvatica_large_bindweed_flower_side_view_27-06-05.jpg]]\n\n\n[[Bindweed|https://en.wikipedia.org/wiki/Calystegia]]\n</center>"},{"id":"2021-02-26T19:48:30.787Z","title":"Garlic Mustard","text":"<center>\n[img[https://upload.wikimedia.org/wikipedia/commons/9/90/Garlic_Mustard_close_800.jpg]]\n\n[img[http://www.kingcounty.gov/~/media/environment/animalsAndPlants/noxious_weeds/imagesD_G/garlic_mustard_flowering.ashx?la=en]]\n\n[[Garlic Mustard|https://en.wikipedia.org/wiki/Alliaria_petiolata]]\n</center>"},{"id":"2021-02-26T19:36:15.484Z","title":"walts test","text":"the quick brown fox"},{"id":"2021-02-17T14:59:34.302Z","title":"Jims tiddler","text":"A note from Jim about google sheets and TW"}]}

The first thing I notice is that the Zotero JSON has this general structure:

[
    {
        ...
    },
	...
]

whereas the generated Sheets JSON has this structure:

{"data":
	[
		{
			...
		},
		...
	]
}

…i.e the Sheets JSON is wrapped with {"data":…}

Q1: Is the “wrapped” aspect a problem for tm-http-request?

Q2: If the wrapping is not the issue:
When I, in the Zotero example, simply switch the url to my sheets JSON url, it doesn’t work. Why? Or, rather, how should I make the message call with my Sheets JSON?

Thank you!

How "split" a tiddler with JSON into individual tiddlers?
#2

Hi @twMat, due to browser security restrictions, you cannot make a cross domain request to a URL that is not served with the appropriate CORS headers, which is why your request is failing. Furthermore, there would be authentication related issues to consider as well.

I suspect you would need to use the Google Sheets API instead. See Google Sheets API Overview  |  Google for Developers and perhaps seom of the workarounds proposed at Did Google Sheets stop allowing json access? - Stack Overflow might be helpful.

#3

No knowledge, but an educated guess. The wrapped aspect should not be a problem, but it will slightly change how you have to interact with the results. Looking at that tiddler, I think it might involve only updating this:

<$list filter="[<data>jsonindexes[]] :map[<data>jsonextract<currentTiddler>,[data]]" variable="item">
	<$macrocall $name="zotero-save-item" item=<<item>>/>
</$list>

to

<$list filter="[<data>jsonget[data]jsonindexes[]] :map[<data>jsonextract<currentTiddler>,[data]]" variable="item">
	<$macrocall $name="zotero-save-item" item=<<item>>/>
</$list>

Note the addition of jsonget[data] near the beginning of the filter.

As I said, though, this is just a – hopefully educated – guess.

But the CORS problem might be a showstopper.

1 Like
#4

If it helps, below is a minimal example of fetching data from a URL. If successful, the data is saved in the tiddler “data”, otherwise an error is shown.

However, CORS errors won’t show any helpful error messages. Other than inspecting response headers (via developer tools), a more layman friendly way to confirm if CORS issues are the problem is to prefix the URL with https://corsproxy.io/? though this too can fail if there are redirects involved. Please use the proxy ONLY for testing.

\procedure completion()
\procedure errorHandler()
<$action-setfield $tiddler="$:/temp/fetcherror" tags="$:/tags/Alert" text=`There was an error fetching the URL: $(error)$, statusText: $(statusText)$, status: $(status)$`/>
\end errorHandler
<$list filter="[<status>match[200]]" emptyMessage=<<errorHandler>>>
<$action-setfield $tiddler="data" text=<<data>>/>
</$list>
\end completion

\procedure fetch-url()
	<$action-sendmessage
		$message="tm-http-request"
		url="https://saq-twlinks.tiddlyhost.com/tiddlers.json"
		method="GET"
		oncompletion=<<completion>>
		onprogress=<<progress>>
	/>
\end

<$button actions=<<fetch-url>>>get json data from url</$button>
1 Like
#5

AHA! - I was really sad after the initial replies here and several failed attempts at various things. But then I saw the bottom post with your code @saqimtiaz which works also with my source url:

As they mention in that stackoverflow thread, the URL has to be (all in one line):

https://sheets.googleapis.com/v4/spreadsheets/
<spreadsheet_id>
/values/
<literal_tab_name>
?alt=json&key=
<api_key>

…and the <api_key> you get by setting it up in your Google cloud setup. So I guess this is what overcomes the CORS matters?

And the Google spreadsheet itself has to be shared (setting: “Anyone with the link”)

…

Well, now the question is instead if the imported JSON tiddler can be processed as when you drag a JSON tiddler into TW - i.e that it actually becomes tiddlers. How is that mechanism accessed with the data tiddler?

#6

Thanks Scott. As you note, that change didn’t overcome the CORS matter, assuming the CORS is indeed what stops it. So I can’t tell if your idea can be used. I appreciate it anyway.

#7

At this point you have JSON in a tiddler and want to import that JSON as individual tiddlers.

The key primitives that you need are the JSON filter operators, the deserialize operator and the import related widget messages.

I recommend making a new post on this topic, specifying whether you want to trigger the default import mechanism or silently create tiddlers from the JSON.

Also, be careful not to post your API key publicly.

1 Like
How "split" a tiddler with JSON into individual tiddlers?
#8

A tip for everyone:

So you can use any publicly shared google sheet without bothering with API keys by using this url

https://opensheet.elk.sh/spreadsheet_id/tab_name

So this demo sheet that I’ve made, which has “tiddler fields” as colum headers, can be inseted into @saqimtiaz code above using this url:

https://opensheet.elk.sh/14ps1hhvhK0ctpH-9k8jOseipdRdTGwF-TnLKoipx7Mk/1

2 Likes