Find a way to display data from a google spreadsheet in vuepress #35

Closed rufuspollock closed 3 years ago

In GitLab by @rufuspollock on Apr 29, 2020, 21:45

Pull data from a google spreadsheet.

When showing the total of endorsers (and some of their names) I want to pull data from spreadsheet (as that is where that info is stored) so that we have that info available in vuepress to display in a component

When displaying the directory of initiatives I want to pull data from a spreadsheet so that non-technical people can manage info in a spreadsheet and we can show it nicely on the website

Acceptance

[ ] A working vuepress example in gitlab.com/datopian/experiments/ pulling data from a sample spreadsheet

Tasks

[x] research on how to pull data from a public google spreadsheet and display them on a vue powered website
[x] find possible solutions
- local xlsx --> JSON --> vue.js
- local JSON --> vue.js
- remote API (with axios) --> vue.js :heavy_check_mark: since it is remote
[x] pick the best one
[x] write down steps
- have a spreadsheet
- 'publish to the web'
- get shareable link
- get the API link
- pull data from the API using axios
[x] try it out
[x] identify problems / complications --> "transforming" the google spreadsheet into API
[x] find possible options (see below)
[x] find the best one (a.k.a. free, no limitations, easy..) --> Google Sheets API e.g. https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key={API key} where spreadsheetId = ID of the spreadsheet, range = relevant cells range e.g. A1%3AC (A1:C) and API key = API key (can be generated here https://developers.google.com/sheets/api/quickstart/js)
[ ] push code somewhere e.g. datopian/vuepress-experiments/...
[ ] apply for our case (with dynamic endorsers)
- [ ] ask for sheet
- [ ] apply

Research

Solution

AXIOS

have a spreadsheet --> get shareable link --> "transform" into API --> use axios to pull data from the API (see code below)

How to

Using `Google Sheets API` :star: (printing in this case column2)

<template>
  <div id="app">
    <h1 class="py-20">Testing</h1>
    <ul>
      <li class="bg-purple-400" v-for="things in things.values">
        {{ things[1] }}
      </li>
    </ul>
  </div>
</template>

<script>
import axios from 'axios';

export default {
  name: 'app',
  data() {
    return {
      things: []
    }
  },
  async created() {
    try {
      const res = await axios.get(`https://sheets.googleapis.com/v4/spreadsheets/16hg-7AavBcRo2CWOnUGpJcoPwEzTERKeft4JdX-1tOo/values/A1%3AC?key=AIzaSyAoc8ZwdOlnnhww-GwAgZpUrb9gcGMmmZ0`)

      this.things = res.data; 
    } catch(e) {
      console.error(e)
    }
  },
}
</script>

<style>

li {
  list-style: none;
}

</style>

Using `sheet.best` (tool for generating API out of google sheet) (printing in this case column2)

<template>
  <div id="app">
    <h1 class="py-20">Testing</h1>
    <ul>
      <li v-for="thing of things">
        {{thing.column2}}
      </li>
    </ul>
  </div>
</template>

<script>
import axios from 'axios';

export default {
  name: 'app',
  data() {
    return {
      things: []
    }
  },
  async created() {
    try {
      const res = await axios.get(`https://sheet.best/api/sheets/09267477-d59c-4411-a7f3-05e697d15294`)

      this.things = res.data;
    } catch(e) {
      console.error(e)
    }
  },
}
</script>

<style>

li {
  list-style: none;
}

</style>

helpful article: https://developer.school/how-to-use-vue-js-json-server-and-axios/

How to "transform" a spreadsheet into API

https://sheet.best/sheet/09267477-d59c-4411-a7f3-05e697d15294 (not the best option - only 2000 requests monthly in the free plan...)
https://www.freecodecamp.org/news/cjn-google-sheets-as-json-endpoint/ (free, need to try it out though..)
Google Sheets API :star: e.g. https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key={API key} where spreadsheetId = ID of the spreadsheet, range = relevant cells range e.g. A1%3AC (A1:C) and API key = API key (can be generated here https://developers.google.com/sheets/api/quickstart/js)

Example

Example table:

exampleTable

Outcome: (using the code above - in this case get only values in column2)

exampleOutcome

Values on the site get updated on refresh when updating the spreadsheet :heavy_check_mark:

In GitLab by @rufuspollock on Apr 29, 2020, 21:57

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:12

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:13

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:13

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:14

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:15

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:17

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:32

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:33

changed the description

In GitLab by @monikappv on Apr 30, 2020, 01:33

@rufuspollock have some small progress on this - you can have a look... will continue tomorrow

In GitLab by @monikappv on Apr 30, 2020, 21:02

changed the description

In GitLab by @monikappv on Apr 30, 2020, 21:09

changed the description

In GitLab by @monikappv on Apr 30, 2020, 21:10

@rufuspollock found a better approach :sparkles: updated the issue description.

can you give me a link to the endorsers spreadsheet so that I can apply it to our case?

In GitLab by @rufuspollock on Apr 30, 2020, 21:56

For now can you make up a spreadsheet:

| Name | Organization | Comment |

Also can we make layout look quite nice e.g. https://www.sustainablewebmanifesto.com/signatures/

BTW there are two options here: list gets updated a) each time we build and b) each time someone visits the page ... At least for numbers i guess we want for b) and probably same for signatories ... but i could like with (a).

PS (separate issue): can you add a share this like at bottom of https://www.sustainablewebmanifesto.com/

In GitLab by @monikappv on May 1, 2020, 10:24

@rufuspollock ok I will

make up such sheet and use it and make a nice layout
add a share at the bottom and open a new issue for that

P.S. locally it gets updated on page refresh, but I have to investigate a bit for the production mode

In GitLab by @rufuspollock on May 3, 2020, 18:30

changed title from Automate display of {-Endorsers-} to Automate display of {+Directory of Initiatives ...+}

In GitLab by @rufuspollock on May 3, 2020, 18:35

changed title from {-Automate display of Directory of Initiatives ...-} to {+Find a way to display data from a google spreadsheet in vuepress+}

In GitLab by @rufuspollock on May 3, 2020, 18:35

changed the description

In GitLab by @rufuspollock on May 3, 2020, 18:35

moved to datopian/tech/breakable-toys#8

life-itself / possibility-now