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.
On 9/10/2021 8:22 AM, dhinostroza wrote:
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
On Wednesday, September 8, 2021 at 6:00:25 PM UTC-5 jjon wrote:
Luis, Daniel, David, et al.
I have an exhibit page
<https://oacurriculumcollection.org/oacc-exhibit/> that worked
fine for a long time using jsonp to retrieve data from a public
google spreadsheet
<https://docs.google.com/spreadsheets/d/1QFApf8MGr-ZP-OqvKtPFUEASuaq9_vuvLOfanC4NGHQ/edit#gid=0>.
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
<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 <http://lmorillas.github.io/exhibit_tests/testcsv/>
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
<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
On Saturday, August 21, 2021 at 9:21:11 AM UTC-7 Luis Miguel
Morillas wrote:
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
<https://gist.github.com/lmorillas/cbf2a0fb4a28ba7d016568ac9269bd37>
[2]
https://docs.google.com/spreadsheets/d/1v-b_AaChE431LvPLlGFSykJFoD--OKRBOjqgiZ7hqPA/edit#gid=180101019
<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
<https://docs.google.com/spreadsheets/d/1v-b_AaChE431LvPLlGFSykJFoD--OKRBOjqgiZ7hqPA/gviz/tq?tqx=out:json>
Saludos,
-- luismiguel (@lmorillas)
El vie, 20 ago 2021 a las 21:46, dhinostroza
(<[email protected]>) escribió:
>
> 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):
> <link
href="https://docs.google.com/spreadsheets/d/e/2PACX-1vSq-fac7XtI1IXXXXXXxxXXXXXxxXXXXXXxXXXXNXXXXXXXXXXXX_XXXXXXXXX_XXXXXXXXXXXXXXXX/pub?gid=1300000000&single=true&output=csv
<https://docs.google.com/spreadsheets/d/e/2PACX-1vSq-fac7XtI1IXXXXXXxxXXXXXxxXXXXXXxXXXXNXXXXXXXXXXXX_XXXXXXXXX_XXXXXXXXXXXXXXXX/pub?gid=1300000000&single=true&output=csv>"
type="text/csv" rel="exhibit/data"
data-ex-properties="label,ID,Lname,Fname,Email,Observations"
data-ex-has-column-titles="true"/>
> I'm using the full path to exhibit-api
(http://api.simile-widgets.org/exhibit/3.1.2rc2/exhibit-api.js
<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
<http://api.simile-widgets.org/exhibit/current/exhibit-api.js>)
has also worked for me in other projects.
> Thank you!
> Daniel
>
> On Friday, August 20, 2021 at 2:01:37 AM UTC-5 Luis Miguel
Morillas wrote:
>>
>> David, I think that it is the old method that has been
removed now.
>>
>> Saludos,
>>
>> -- luismiguel (@lmorillas)
>>
>> El vie, 20 ago 2021 a las 4:08, David Karger
(<[email protected]>) escribió:
>> >
>> > According to this article:
>> >
>> > https://benborgers.com/posts/google-sheets-json
<https://benborgers.com/posts/google-sheets-json>
>> >
>> > the following should work:
>> >
>> >
https://spreadsheets.google.com/feeds/list/spreadsheet_key/1/public/values?alt=json
<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
>> >
>> >
>> > On 8/18/2021 11:08 AM, Luis Miguel Morillas wrote:
>> > > 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
<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
<https://developers.google.com/gdata/docs/json>
>> > >
>> > > Saludos,
>> > >
>> > > -- luismiguel (@lmorillas)
>> > >
>> > > El mié, 18 ago 2021 a las 15:13, Andrea Borruso
(<[email protected]>) escribió:
>> > >> 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
<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
>> > >>
>> > >> --
>> > >> 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
[email protected].
>> > >> To view this discussion on the web visit
https://groups.google.com/d/msgid/simile-widgets/8b661bc0-6887-4491-8349-34fb0f290a25n%40googlegroups.com
<https://groups.google.com/d/msgid/simile-widgets/8b661bc0-6887-4491-8349-34fb0f290a25n%40googlegroups.com>.
>> >
>> > --
>> > 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 [email protected].
>> > To view this discussion on the web visit
https://groups.google.com/d/msgid/simile-widgets/43e5cf7b-783f-68fb-129f-e93e33f7ddfa%40mit.edu
<https://groups.google.com/d/msgid/simile-widgets/43e5cf7b-783f-68fb-129f-e93e33f7ddfa%40mit.edu>.
>
> --
> 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 [email protected].
> To view this discussion on the web visit
https://groups.google.com/d/msgid/simile-widgets/0caa446b-b900-49f6-8138-feacb2531057n%40googlegroups.com
<https://groups.google.com/d/msgid/simile-widgets/0caa446b-b900-49f6-8138-feacb2531057n%40googlegroups.com>.
--
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 [email protected]
<mailto:[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/simile-widgets/9dbce95b-7e4b-46ed-8d59-d8a123955c67n%40googlegroups.com
<https://groups.google.com/d/msgid/simile-widgets/9dbce95b-7e4b-46ed-8d59-d8a123955c67n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/simile-widgets/6ff37b7d-e54a-6419-50d4-860141a6e029%40mit.edu.
