what's the new google sheet JSON output URL?

Andrea Borruso

unread,

Aug 18, 2021, 9:12:58 AM8/18/21

to SIMILE Widgets

Hi,

I used for some simile pages this kind of URL:

https://spreadsheets.google.com/feeds/list/1H38J-cLEAt8VhDuhMvJgSGH4KzT0LrhFl9tvGTQpqWI/od6/public/basic?alt=json-in-script

But none of these URLs work anymore.

What's the new URL schema for this kind of output?

Thank you

Luis Miguel Morillas

unread,

Aug 18, 2021, 11:08:33 AM8/18/21

to simile-...@googlegroups.com

I'm afraid the Data API is down [1]. Look at
https://docs.google.com/feeds/list/1H38J-cLEAt8VhDuhMvJgSGH4KzT0LrhFl9tvGTQpqWI/1/public/values?alt=json-in-script
You can use csv export either.

[1] https://developers.google.com/gdata/docs/json

Saludos,

-- luismiguel (@lmorillas)

> --
> You received this message because you are subscribed to the Google Groups "SIMILE Widgets" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to simile-widget...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/8b661bc0-6887-4491-8349-34fb0f290a25n%40googlegroups.com.

dhinostroza

unread,

Aug 19, 2021, 2:39:00 PM8/19/21

to SIMILE Widgets

Hola, Luis Miguel:

Can you please provide a web page of yours (or the url) with your solution?

Very kind regards,

Daniel

Luis Miguel Morillas

unread,

Aug 19, 2021, 5:00:28 PM8/19/21

to simile-...@googlegroups.com

Hi, Daniel,

What kind of example? Data from a google spreadsheet read as csv?

Luis Miguel

To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/33f36264-5cf6-43db-a092-6d0edbb3385fn%40googlegroups.com.

dhinostroza

unread,

Aug 19, 2021, 7:39:55 PM8/19/21

to SIMILE Widgets

Hi, Luis Miguel

Yes, I have many web pages that depend on Google spreadsheets. I'd like to either migrate them to link to the new API (better) or download a .csv file and link to it.

Thank you,

Daniel

David Karger

unread,

Aug 19, 2021, 10:08:04 PM8/19/21

to simile-...@googlegroups.com

According to this article:

https://benborgers.com/posts/google-sheets-json

the following should work:

https://spreadsheets.google.com/feeds/list/spreadsheet_key/1/public/values?alt=json

that is, replace /od6/public/basic?alt=json-in-script at the end with
/1/public/values?alt=json

Luis Miguel Morillas

unread,

Aug 20, 2021, 2:37:24 AM8/20/21

to simile-...@googlegroups.com

This example should work http://lmorillas.github.io/exhibit_tests/testcsv/

It is a rewrite of the official demo of exhibit with csv.

Saludos,

-- luismiguel (@lmorillas)

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/518f5fe4-6574-460c-9219-a120ab26c4ccn%40googlegroups.com.

Luis Miguel Morillas

unread,

Aug 20, 2021, 3:01:37 AM8/20/21

to simile-...@googlegroups.com

David, I think that it is the old method that has been removed now.

Saludos,

-- luismiguel (@lmorillas)

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/43e5cf7b-783f-68fb-129f-e93e33f7ddfa%40mit.edu.

dhinostroza

unread,

Aug 20, 2021, 3:46:11 PM8/20/21

to SIMILE Widgets

Hi Luis Miguel and David,

I tested Ben Borgers solution first and it didn't work on the first go. Maybe it's just a matter of tweaking it a bit further to get the settings right...

So, I then tested Luis Miguel's solution (publishing the Google spreadsheet to csv and replacing the json link with the full published as csv link in my SIMILE page) and it worked perfectly.

It looks like this (I made up the column titles for this example):

I'm using the full path to exhibit-api (http://api.simile-widgets.org/exhibit/3.1.2rc2/exhibit-api.js) url but the shortened (//api.simile-widgets.org/exhibit/current/exhibit-api.js) has also worked for me in other projects.

Thank you!

Daniel

Luis Miguel Morillas

unread,

Aug 21, 2021, 12:21:11 PM8/21/21

to simile-...@googlegroups.com

Hola, Daniel,

Well, the first approach, as Ben says, works fine now, but it returns
a json that you must arrange to work with exhibit[1] if you use this
spreadsheet for example [2] with this url [3] as Ben suggests.

I think it's easier to use csv from google spreadsheets directly. Or
you can get the csv and convert internally to json, but exhibit can
read right the csv.

[1] https://gist.github.com/lmorillas/cbf2a0fb4a28ba7d016568ac9269bd37
[2] https://docs.google.com/spreadsheets/d/1v-b_AaChE431LvPLlGFSykJFoD--OKRBOjqgiZ7hqPA/edit#gid=180101019
[3] https://docs.google.com/spreadsheets/d/1v-b_AaChE431LvPLlGFSykJFoD--OKRBOjqgiZ7hqPA/gviz/tq?tqx=out:json

Saludos,

-- luismiguel (@lmorillas)

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/0caa446b-b900-49f6-8138-feacb2531057n%40googlegroups.com.

jjon

unread,

Sep 8, 2021, 7:00:25 PM9/8/21

to SIMILE Widgets

Luis, Daniel, David, et al.

I have an exhibit page that worked fine for a long time using jsonp to retrieve data from a public google spreadsheet. The php generated a <link/> in the head like this:
```<link rel="exhibit/data"
        type="application/jsonp"
        href="https://spreadsheets.google.com/feeds/list/1QFApf8MGr-ZP-OqvKtPFUEASuaq9_vuvLOfanC4NGHQ/od6/public/basic?alt=json-in-script"
        ex:converter="googleSpreadsheets" />```

That doesn't work any more.

Following your discussion, I tried to find a way to fetch and parse the data as csv.

Like Daniel I thought Ben Borgers solution might take too much fiddleing (just hacking off the function call seems, well, hacky).

Luis's example doesn't seem to work either.

I finally figured out a url that would return the spreadsheet data as csv. This is what the <link/> element now looks like:

```<link
            href="https://docs.google.com/spreadsheets/d/1QFApf8MGr-ZP-OqvKtPFUEASuaq9_vuvLOfanC4NGHQ/export?format=csv"
            type="text/csv"
            rel="exhibit/data"

            data-ex-properties="label,itemID,Link1,Link2,Link3,Short_Description:single,Long_Description:single,Developer,Category,Activity_Type,imageURL,Tags,Grade_Band,OA_Principle"
            data-ex-has-column-titles="true"
        />

This successfully retrieves csv from the spreadsheet, but Exhibit does not seem to parse it correctly.

On load, in the console I get, among other things:
a warning: Item entry has no label and no id: {} debug.js:57:21
a report: Failed to create view Summary (0) debug.js:35:21
and an error: TypeError: Exhibit.Formatter._constructors[valueType] is not a constructor. ...debug.js:90:25

any clues as to how I might repair this would be welcome.

best

Jon

Luis Miguel Morillas

unread,

Sep 9, 2021, 1:58:50 AM9/9/21

to simile-...@googlegroups.com

Sorry, I ended sharing the spreadsheet. Reload the page now[1]. I'll
take a look to your page later.

[1] http://lmorillas.github.io/exhibit_tests/testcsv/

Saludos,

-- luismiguel (@lmorillas)

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/21b011f7-c59b-4ee5-9107-c15d8e681a36n%40googlegroups.com.

dhinostroza

unread,

Sep 10, 2021, 8:22:07 AM9/10/21

to SIMILE Widgets

Hi Jon,

I initially couldn't load the csv in the proper order either. I always leave column A blank in my spreadsheets, to avoid any unwanted deletions while opening the file. I removed the blank column A and moved any columns not being used by Exhibit to the end of the spreadsheet. Now it's loading the csv in the correct order. I haven't tested them separately to see if both changes are needed. Hope this is what you need!

Best,

Daniel

dhinostroza

unread,

Sep 10, 2021, 8:25:39 AM9/10/21

to SIMILE Widgets

Oh, and json doesn't need to have the columns in the same order as the exhibit, but csv does. I think.

On Wednesday, September 8, 2021 at 6:00:25 PM UTC-5 jjon wrote:

David Karger

unread,

Sep 10, 2021, 8:33:03 AM9/10/21

to simile-...@googlegroups.com

In situations where the spreadsheet has characteristics that prevent it from loading, I generally solve the problem by adding a second worksheet to the spreadsheet, which copies the columns I need from the original sheet using array formulas. And sometimes additional computer columns. Then I export the copied worksheet so I don't have to change the original.

To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/9dbce95b-7e4b-46ed-8d59-d8a123955c67n%40googlegroups.com.

dhinostroza

unread,

Sep 10, 2021, 8:34:11 AM9/10/21

to SIMILE Widgets

I saw it now. Your link to the spreadsheet is missing some data. It should look like this:
<link

href="https://docs.google.com/spreadsheets/d/1QFApf8MGr-ZP-OqvKtPFUEASuaq9_vuvLOfanC4NGHQ/pub?gid=1321450045&single=true&output=csv"

type="text/csv"

rel="exhibit/data"

data-ex-properties=...

and my previous suggestions.

All the very best,

Daniel

On Wednesday, September 8, 2021 at 6:00:25 PM UTC-5 jjon wrote:

Luis Miguel Morillas

unread,

Sep 11, 2021, 1:56:59 PM9/11/21

to simile-...@googlegroups.com

I've simplified your page. I don't know if it's you need. The example
uses your spreadsheet and a simplified version of your html page
translated to exhibit 3 syntax [1]

[1] http://lmorillas.github.io/exhibit_tests/testoacurriculum/

Saludos,

-- luismiguel (@lmorillas)

El jue, 9 sept 2021 a las 1:00, jjon (<crum...@gmail.com>) escribió:
>

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/21b011f7-c59b-4ee5-9107-c15d8e681a36n%40googlegroups.com.

jjon

unread,

Sep 12, 2021, 4:05:44 PM9/12/21

to SIMILE Widgets

Thanks Luis,

It's helpful to see that a simplified version of the page can be built from the data retrieved via csv; however, I'd like to track down what about the csv data is causing the TypeError: `TypeError: Exhibit.Formatter._constructors[valueType]`.

For this wordpress page, I've been using a locally sourced copy of the Exhibit code base, this copy is the same one as at http://api.simile-widgets.org/exhibit/current/exhibit-api.js (3.1.2-rc3). Before the jsonp method stopped working, this version was understanding my unfortunate Exhibit2 syntax just fine. Just in case, I went in and updated all the ver2 tag attribute syntax, but that didn't solve the problem.

The fact that your simplified and updated page also does not correctly interpret the semi-colons in cells as multiple values is also suggestive. Of what, I don't know yet. I need to understand the code base better I guess. I'm pretty far out on the edge of my understanding. Something about the csv data, vs the json data, throws the TypeError. I need to understand why the Exhibit.Formatter constructors don't recognize the `valueType` they're being given. Then I can change the spreadsheet accordingly, or compensate for it in the exhibit html, or maybe do some kind of js preprocessing of the incoming csv.

Still puzzled,

Jon

Luis Miguel Morillas

unread,

Sep 13, 2021, 1:09:05 AM9/13/21

to simile-...@googlegroups.com, lmorillas

El dom, 12 sept 2021 a las 22:05, jjon (<crum...@gmail.com>) escribió:
>
> Thanks Luis,
> It's helpful to see that a simplified version of the page can be built from the data retrieved via csv; however, I'd like to track down what about the csv data is causing the TypeError: `TypeError: Exhibit.Formatter._constructors[valueType]`.

OK. I see. The simplied version removed all external js because maybe
there is some kind race condition conflict in your code.

> For this wordpress page, I've been using a locally sourced copy of the Exhibit code base, this copy is the same one as at http://api.simile-widgets.org/exhibit/current/exhibit-api.js (3.1.2-rc3). Before the jsonp method stopped working, this version was understanding my unfortunate Exhibit2 syntax just fine. Just in case, I went in and updated all the ver2 tag attribute syntax, but that didn't solve the problem.
>
> The fact that your simplified and updated page also does not correctly interpret the semi-colons in cells as multiple values is also suggestive. Of what, I don't know yet. I need to understand the code base better I guess. I'm pretty far out on the edge of my understanding. Something about the csv data, vs the json data, throws the TypeError. I need to understand why the Exhibit.Formatter constructors don't recognize the `valueType` they're being given. Then I can change the spreadsheet accordingly, or compensate for it in the exhibit html, or maybe do some kind of js preprocessing of the incoming csv.

You can use data-ex-value-separator=";" for multiple values.

>
> Still puzzled,
Tell me if I can help

Best,

-- @lmorillas

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/694d72dd-d15b-46dd-9255-8b7222b8da44n%40googlegroups.com.

Luis Miguel Morillas

unread,

Sep 13, 2021, 1:12:34 AM9/13/21

to simile-...@googlegroups.com, lmorillas

Added multiple-values parsing to
https://lmorillas.github.io/exhibit_tests/testoacurriculum/

Saludos,

-- luismiguel (@lmorillas)

El lun, 13 sept 2021 a las 7:08, Luis Miguel Morillas
(<mori...@gmail.com>) escribió:

jjon

unread,

Sep 18, 2021, 7:18:18 PM9/18/21

to SIMILE Widgets

Luis, Daniel, David, et al.

In the context of my WordPress site, I have a functions.php file that loads the locally held simile software up front, thus:
```
function my_scripts_method() {
wp_enqueue_script( 'exhibit-api.js', get_stylesheet_directory_uri() . '/js/exhibit-dist/exhibit-api.js?bundle=false');
wp_enqueue_script( 'more_custom_JS', get_stylesheet_directory_uri() . '/js/custom-scripts/customscripts.js', array( 'jquery' ));
}
add_action( 'wp_enqueue_scripts', 'my_scripts_method' );
```

This works fine: Exhibit is loaded and appears to be complete and correct. I did try commenting out that php and putting the simile-widgets.org link in the <head/> of the page instead: ```<script src="//api.simile-widgets.org/exhibit/current/exhibit-api.js"></script>```. This works fine too but no matter what the source of the exhibit code, and no matter what the data, I still keep getting the ```TypeError: Exhibit.Formatter._constructors[valueType] is not a constructor```.

(Note: there's now a test case using the Italian football data that can be seen at oacurriculumcollection.org/exhibit-test, in addition to the page at oacurriculumcollection.org/oacc-exhibit)

I've tried my csv data from a gsheet, and I used the gsheet data from the sample site that Luis has mentioned http://lmorillas.github.io/exhibit_tests/testcsv/. In both cases it's clear that the data is retrieved successfully, the database if created, and, so far as I can tell, the data in it is complete and correct. But when exhibit tries to deploy it on the page, I get the same `TypeError`.

The old jsonp method, using data-ex-converter="googleSpreadsheets" worked fine in the context of my php page, but there's something about the retrieved csv data in this context that continues to throw this error.

The full traceback for this error is the same in every instance. It is appended below.

I was Curious to know what `valueType` it is for which there is no constructor. I wish I were more adept with the browser's debugger (such tools always present me with more confusion than clarity), so I put a console.log() call into ".../exhibit/scripts/ui/lens.js" at line 1007 in the `Exhibit.Lens._constructDefaultValueList` function (the 5th item in the traceback included below). The result was that for any given record in the spreadsheet, two `valueType`s were reported: "item", and "single". That was interesting, but I'm really none the wiser. The list of `Exhibit.Formatter._constructor`s does include "item", it does not include "single", but I don't know how these values are being derived from the csv data, or how they are supposed to be deployed in the UI.

Something there is that doesn't love a csv input in my particular context, and I'm damned if I know what.

If you've any further thoughts at all about this, I'd love to know.

best,

Jon

***
browser console traceback for the TypeError:
```
debug.js:90:25

TypeError: Exhibit.Formatter._constructors[valueType] is not a constructor

Luis Miguel Morillas

unread,

Sep 19, 2021, 2:49:15 AM9/19/21

to simile-...@googlegroups.com

The issue may be a race condition. You see the page well loaded but
when exhibit loads programmatically, the sources of the page could not
be loaded and you must control the load process.
What about if you load the exhibit lib with "?autoCreate=false" and
then you run "Exhibit.autoCreate()" programatically fired by a page
event ("staticComponentsRegistered.exhibit") Look at
https://github.com/simile-widgets/exhibit/wiki/Scripted-Event-API

If csv version works fine in a simplified example (the one I wrote) it
must work too in your complex site, but there are other factors, i.e.
to load exhibit lib by a php function.

Best,

-- luismiguel (@lmorillas)

> To view this discussion on the web visit https://groups.google.com/d/msgid/simile-widgets/5deb6891-cad9-4c35-89a8-210a0093d787n%40googlegroups.com.

jjon

unread,

Sep 22, 2021, 10:01:40 PM9/22/21

to SIMILE Widgets

Luis,

My thanks to you. There was no race condition. Once I got the csv link right, and cleaned up some broken exhibit2 stuff, autoCreate operates as expected. I never did identify where the specific type error was coming from, but thanks to your working, simplified, html version of my page, I was finally able to rebuild its original functionality within the php template, now using csv from google sheets, instead of the old jsonp method. And I did a lot of cleaning up in the process. There are still some infelicities in the repaired page that I haven't fixed yet (for example, the "bookmark" function seems to be broken), but the test site for the repaired page can be seen here.