Giter VIP home page Giter VIP logo

gatsby-source-google-sheets's Introduction

gatsby-source-google-sheets

Why go through the hassle of setting up a complicated headless CMS when Google Sheets already has user permissions, revision history, and a powerful UI?

This source plugin for Gatsby JS will turn any Google Sheets worksheet into a GraphQL type for build-time consumption.

How to:

Step 1: set up sheets/permissions

  1. Create a Google Service Account and download the credentials file.
  2. Open your google sheet, click "File > Share..." and enter your service account's e-mail address (you can find it in the credentials file).

Step 2: configure your gatsby project

Standard source plugin installation.

yarn add gatsby-source-google-sheets


// gatsby-config.js
// ...
{
    resolve: 'gatsby-source-google-sheets',
    options: {
        spreadsheetId: 'get this from the sheet url',
        worksheetTitle: 'ie the name in the worksheet tab',
        credentials: require('./path-to-credentials-file.json')
    }
},
// ...

The plugin makes the following conversions before feeding Gatsby nodes:

  1. Numbers are converted to numbers. Sheets formats numbers as comma-delineated strings, so to determine if something is a number, the plugin tests to see if the string (a) is non-empty and (b) is composed only of commas, decimals, and digits:
if (
    "value".replace(/[,\.\d]/g, "").length === 0 
      && "value" !== ""
   ) { 
    ...assume value is a number and handle accordingly
}
  1. "TRUE"/"FALSE" converted to boolean true/false
  2. empty cells ("" in sheets payload) converted to null
  3. Column names are converted to camelcase via lodash _.camelCase() (see note 2 in 'A few notes')

A few notes:

  1. Not tested with cells of data type dates.
  2. Google sheets mangles column names and converts them all to lower case. This plugin will convert them to camelcase, so the best convention here is to name your columns all lowercase with dashes. e.g. instead of "Column Name 1" or "columnName1", prefer "column-name-1"--this last one will be turned into "columnName1" in your GatsbyQL graph.

Troubleshooting

  1. If you get the error "No key or keyFile set", make sure you are using a Service Account API key and not a simple API key.
  2. If you get the error "Cannot read property 'worksheets' of undefined", make sure you have shared your spreadsheet with your service account user.

gatsby-source-google-sheets's People

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar

gatsby-source-google-sheets's Issues

Changes to the g-sheet data is not reflected in graphiql query result

Hi,
Thanks for this plugin.
Curious to know how i can make it realtime synching of data from my g-sheet. Right now if i update the data to the g-sheet then its not reflecting in my graphiql query result unless i restart the application. Is there anything i am missing to configure. Thanks in advance

TypeError: fetchSheet is not a function

After following your setup instructions, I just get the following error:

error Plugin gatsby-source-google-sheets returned an error


  TypeError: fetchSheet is not a function
  
  - gatsby-node.js:35 _callee$
    [me]/[gatsby-source-google-sheets]/gatsby-node.js:35:20
  
  - gatsby-node.js:62 Object.sourceNodes
    [me]/[gatsby-source-google-sheets]/gatsby-node.js:62:18
  
  - api-runner-node.js:110 runAPI
    [me]/[gatsby]/dist/utils/api-runner-node.js:110:36
  
  - api-runner-node.js:187 
    [me]/[gatsby]/dist/utils/api-runner-node.js:187:33
  
  - map.js:27 
    [me]/[gatsby]/[async]/internal/map.js:27:9
  
  - eachOfLimit.js:64 replenish
    [me]/[gatsby]/[async]/internal/eachOfLimit.js:64:17
  
  - eachOfLimit.js:49 iterateeCallback
    [me]/[gatsby]/[async]/internal/eachOfLimit.js:49:17
  
  - onlyOnce.js:12 
    [me]/[gatsby]/[async]/internal/onlyOnce.js:12:16
  
  - map.js:29 
    [me]/[gatsby]/[async]/internal/map.js:29:13
  
  - util.js:16 tryCatcher
    [me]/[bluebird]/js/release/util.js:16:23
  
  - nodeify.js:23 Promise.successAdapter
    [me]/[bluebird]/js/release/nodeify.js:23:30
  
  - promise.js:566 Promise._settlePromise
    [me]/[bluebird]/js/release/promise.js:566:21
  
  - promise.js:606 Promise._settlePromiseCtx
    [me]/[bluebird]/js/release/promise.js:606:10
  
  - async.js:138 Async._drainQueue
    [me]/[bluebird]/js/release/async.js:138:12
  
  - async.js:143 Async._drainQueues
    [me]/[bluebird]/js/release/async.js:143:10

Cannot read property 'createNode' of undefined (boundActionCreators.createNode) - GatsbyJS 3.2.1

Dear maintainer,
I faced in this issue after installing the plugin and followed the instructions as reported in the documentation.

`
ERROR #11321 PLUGIN

"gatsby-source-google-sheets" threw an error while running the
sourceNodes lifecycle:

Cannot read property 'createNode' of undefined

32 | switch (_context.prev = _context.next) {
33 | case 0:

34 | createNode = boundActionCreators.createNode;
| ^
35 |
36 | console.log("FETCHING SHEET", fetchSheet);
37 | _context.next = 4;

File: node_modules\gatsby-source-google-sheets\gatsby-node.js:34:46
`

Below list of modules installed in my project:

{ "name": "gatsby-starter-default", "private": true, "description": "A simple starter to get up and developing quickly with Gatsby", "version": "0.1.0", "author": "Kyle Mathews <[email protected]>", "dependencies": { "babel-plugin-styled-components": "^1.12.0", "framer-motion": "^4.1.2", "gatsby": "^3.2.1", "gatsby-plugin-gatsby-cloud": "^2.2.0", "gatsby-plugin-image": "^1.2.0", "gatsby-plugin-manifest": "^3.2.0", "gatsby-plugin-offline": "^4.2.0", "gatsby-plugin-react-helmet": "^4.2.0", "gatsby-plugin-sharp": "^3.2.0", "gatsby-plugin-styled-components": "^4.2.0", "gatsby-source-contentful": "^5.2.0", "gatsby-source-filesystem": "^3.2.0", "gatsby-source-google-sheets": "^1.1.1", "gatsby-transformer-sharp": "^3.2.0", "prop-types": "^15.7.2", "react": "^17.0.1", "react-dom": "^17.0.1", "react-helmet": "^6.1.0", "styled-components": "^5.2.3", "styled-reset": "^4.3.4", "use-deep-compare-effect": "^1.6.1" }, "devDependencies": { "prettier": "2.2.1" }, "keywords": [ "gatsby" ], "license": "0BSD", "scripts": { "build": "gatsby build", "develop": "gatsby develop", "format": "prettier --write \"**/*.{js,jsx,ts,tsx,json,md}\"", "start": "npm run develop", "serve": "gatsby serve", "clean": "gatsby clean", "test": "echo \"Write tests! -> https://gatsby.dev/unit-testing\" && exit 1" }, "repository": { "type": "git", "url": "https://github.com/gatsbyjs/gatsby-starter-default" }, "bugs": { "url": "https://github.com/gatsbyjs/gatsby/issues" } }

Camel Case not working on normal strings "User email"

I think you stated this in the readme, maybe instead of lodash you could use the camelcase npm module https://www.npmjs.com/package/camelcase. I think it works on normal strings just fine and many others. It is actually what I use for my gatsby plugins to camelcase data before putting it into graphql.

const camelCase = require('camelcase');
type: camelCase(`googleSheet ${worksheetTitle} row`),

That should make strings like "User email" into userEmail.

Accessing client_secret.json

Is there any way to access the client_secret.json file when hosting on a service such as netlify. Obviously this file shouldn't be committed to the repo so how would you go about accessing it? Or is there another way to configure the credentials? In a .env file perhaps?

What happens with bold/italic text?

Hello,

thank you for this awesome plugin. I was wondering what happens when it meets formatted text in a spreadsheet cell. As far as I could test, it just ignores it.

Am I right?

Thank you.

How do I query from the plugin?

I am fairly new to Gatsby and GraphQL as a whole. I got all of the setup work done for this plugin to get permissions working. I'm unsure how to actually write the GraphQL query though. Anyone have any insight?

Naming the first column of a sheet "id" breaks the plugin

If the first column of a sheet is called id, the plugin breaks with something like this

"gatsby-source-google-sheets" threw an error while running the sourceNodes lifecycle:

value must be an array of bytes



  TypeError: value must be an array of bytes
  
  - v35.js:29 generateUUID
    [running]/[uuid]/lib/v35.js:29:38
  
  - gatsby-node.js:45 
    [running]/[gatsby-source-google-sheets]/gatsby-node.js:45:2    1
  
  - Array.forEach
  
  - gatsby-node.js:43 _callee$
    [running]/[gatsby-source-google-sheets]/gatsby-node.js:43:1    8
  
  - task_queues.js:82 processTicksAndRejections
    internal/process/task_queues.js:82:5

New to Gatsby, how can I access data?

I've completed the configuration and realised it is not specified that how to access the fetched data in docs. Or am I missing something. Can someone please shed some light? I assumes it'll be a graphql query, but how exactly?

Documentation updates

We need to update documentation to make this a bit better on beginners

  • How to query for data, and what fields are available: #21 #13
  • Limitations of the google sheets api: #20 #17

How does this perform with super large spreadsheets?

Hi, big fan of this plugin and have been using it on my site for several months now.

I was wondering - I'm looking to add a new sheet which could contain over 1000 rows and 30-40 columns - would this plugin be able to cope with that? Would it have a huge affect on build times etc.?

I need to deploy this site quite regularly and I need build times to be no more than a few minutes.

Any help or info would be much appreciated. Thanks!

"The gatsby-source-google-sheets plugin has generated no Gatsby nodes" when sheet has a single row

Steps to reproduce:

  1. Query a spreadsheet that only has a single row
  2. gatsby develop
success open and validate gatsby-config — 0.007 s
...
⠁ FETCHING SHEET function fetchData(_x2, _x3, _x4) {
    return _ref.apply(this, arguments);
  }
warning The gatsby-source-google-sheets plugin has generated no Gatsby nodes. Do you need it?
success source and transform nodes — 1.032 s
...

And indeed, graphiql shows me that no node are created. It works fine with sheets that contain at least two rows.

TypeError: Cannot read property 'worksheets' of undefined

Hi all! I know this Error is highlighted at the bottom of the README under Troubleshooting.

  • If you get the error "Cannot read property 'worksheets' of undefined", make sure you have shared your spreadsheet with your service account user.

Followed the troubleshooting suggestions and I am still left with the error.

I weirdly received this error yesterday, and made sure that the service account user had been shared with the spreadsheet. Creating a new service account however seemed to temporary fix the issue until now.

Getting the same error and creating a new service account is not able to fix the issue this time.

Let me know if theres any further information I can provide! Any help is much appreciated! Thank you!

Cells containing only emoji characters are mapped to 0 number

Regex here https://github.com/brandonmp/gatsby-source-google-sheets/blob/master/src/fetch-sheet.js#L57 will remove emoji from value and if cell contains only emoji this will leave empty string which will be treated as number (!isNaN('') === true) and value will be converted to 0.

This breaks type infering for that column in gatsby -
gatsbyjs/gatsby#3913

Sheet that can be used to reproduce - https://docs.google.com/spreadsheets/d/19Q6wT_RKouS9PQTBxsAiek8fv-fSpCzO2zXB1tL_W-Y/edit#gid=0 (cell E57).

I'm not familiar with google sheets api. Is there a way to get numeric value without applied formatting? It seems that it's not possible when using getRows function.

Will Google Sheets API v4 break this plugin?

v3 of Google Sheets API will be deprecated in March, and features that this plugin are reliant on may be getting discontinued.

Do we know if this plugin will still function when this happens? Or will a major overhaul be required?

Note: See this twitter thread.

There are conflicting field types in your data. GraphQL schema will omit those fields.

I had a problem that column in my spreadsheet contained data:

1
2
3
5/6

First three rows were parsed as number but the last column was parsed as string. Which caused issue when loading data in GraphQL

warning There are conflicting field types in your data. GraphQL schema will omit those fields.
11:37:47 PM: spreadsheetRow.myColumn:
11:37:47 PM:  - type: number
11:37:47 PM:    value: 1
11:37:47 PM:  - type: string
11:37:47 PM:    value: '5/6'
error GraphQL Error Unknown field `myColumn` on type `spreadsheetRow`

Solution to this problem is to determine column type by content of all cells.

I was able to fix that. PR is coming ⏱

Warnings when installing version "^1.1.1"

Hey @brandonmp - Picking up a lot of deprecated warnings. I noticed the last commit to master was 10 days ago, but don't think another release has been cut in a while. What are the plans to cut another release and does it address these warnings?

warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > [email protected]: This version has been deprecated. Please upgrade to the latest version to get the best features, bug fixes, and security patches.
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > [email protected]: Use uuid module instead
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > [email protected]: This version has been deprecated. Please upgrade to the latest version to get the best features, bug fixes, and security patches.
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > sntp > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > boom > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
warning gatsby-source-google-sheets > google-spreadsheet > google-auth-library > request > hawk > cryptiles > [email protected]: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).

Maintenance?

@brandonmp this is a great plugin, I use it in several projects, so thanks for building it 😃 But it looks like it's not maintained anymore? Do you have any thoughts? Are you using it yourself?

How to query from Google Sheet

Issue

I've set up my repository up to step 2, with the necessary information within gatsby-config.js I have also managed to gastby-develop successfully. The question now is, how do I query data from my google sheet?

Any form of help is appreciated, even if it's just pointing me in the right direction so that I can figure things out.

Columns with fields containing 0 ignored

I just spent the better part of an hour scratching my head on why just one column (a rating from 0 to 5) was consistently being ignored by graphql. Changing the few fields with a 0 value to 1 (or any other int) solved the problem. Maybe this occurs because of Gatsbyjs's auto schema creation? If so (or otherwise) how could this be worked around?

Error with worksheets

Hello ✌️

I'm getting an error and I already try with lots of things, can anyone help me ?

Negative number is not recognized as a number

Thanks for this awesome plugin! I use it to get my swarm check-ins from a google sheet. Sometimes the coordinate are negative and it results in the error and consequently in omitting these columns at all.

googleSheetSwarmCheckinsRow.longitude:

  • type: number
    value: 50.36813591
  • type: string
    value: '-6.121681532'
    googleSheetSwarmCheckinsRow.latitude:
  • type: number
    value: 15.17281175
  • type: string
    value: '-0.1278404805'

Mixed field types

Hello, I am trying to receive data from a list of shops. The column "name" contains the names of all shops. I have a name that is called 128 and I think is breaking the plugin:

I receive the following error:

warn There are conflicting field types in your data.

If you have explicitly defined a type for those fields, you can safely ignore this warning message.
Otherwise, Gatsby will omit those fields from the GraphQL schema.

If you know all field types in advance, the best strategy is to explicitly define them with the `createTypes` action, and skip inference with the `@dontInfer` directive.
See https://www.gatsbyjs.org/docs/actions/#createTypes
googleSheetEsercentiRow.name:
 - type: number
   value: 128
 - type: string
   value: 'Clothing Company'

error thrown during sourceNode lifecycle: "TypeError: value must be an array of bytes"

When the spreadsheet has an id column, gatsby develop emits this error:

15:55 $ gatsby develop
success open and validate gatsby-configs - 0.012 s
success load plugins - 0.225 s
success onPreInit - 0.057 s
success initialize cache - 0.004 s
success copy gatsby files - 0.009 s
success onPreBootstrap - 0.006 s
FETCHING SHEET function fetchData(_x2, _x3, _x4) {
    return _ref.apply(this, arguments);
  }

 ERROR #11321  PLUGIN

"gatsby-source-google-sheets" threw an error while running the sourceNodes lifecycle:

value must be an array of bytes



  TypeError: value must be an array of bytes
  
  - v35.js:29 generateUUID
    [fgbg]/[uuid]/lib/v35.js:29:38
  
  - gatsby-node.js:45 
    [fgbg]/[gatsby-source-google-sheets]/gatsby-node.js:45:21
  
  - Array.forEach
  
  - gatsby-node.js:43 _callee$
    [fgbg]/[gatsby-source-google-sheets]/gatsby-node.js:43:18
  
  
  - next_tick.js:189 process._tickCallback
    internal/process/next_tick.js:189:7
  

warn The gatsby-source-google-sheets plugin has generated no Gatsby nodes. Do you need it?

This is because the row objects have an id property, and it looks like the id column in the spreadsheet clobbers it. Removing the id column (I just made my column name more specific, in my case level-id) fixes the issue.

If you want, I could send in a PR where detect the presence of an id column and fail with a more descriptive/helpful error.

Thanks for this cool plugin!

value must be an array of bytes on uuid

ERROR #11321 PLUGIN

"gatsby-source-google-sheets" threw an error while running the sourceNodes lifecycle:

value must be an array of bytes

43 | rows.forEach(function (r) {
44 | createNode(Object.assign(r, {

45 | id: uuidv5(r.id, uuidv5("gsheet", seedConstant)),
| ^
46 | parent: "SOURCE",
47 | children: [],
48 | internal: {

File: node_modules/gatsby-source-google-sheets/gatsby-node.js:45:21

TypeError: value must be an array of bytes

  • v35.js:29 generateUUID
    [gatsby-site]/[uuid]/lib/v35.js:29:38

  • gatsby-node.js:45
    [gatsby-site]/[gatsby-source-google-sheets]/gatsby-node.js:45:21

  • Array.forEach

  • gatsby-node.js:43 _callee$
    [gatsby-site]/[gatsby-source-google-sheets]/gatsby-node.js:43:18

  • next_tick.js:68 process._tickCallback
    internal/process/next_tick.js:68:7

  • From previous event:

  • api-runner-node.js:440 resolve
    [gatsby-site]/[gatsby]/src/utils/api-runner-node.js:440:9

  • From previous event:

  • api-runner-node.js:439 Promise.mapSeries.plugin
    [gatsby-site]/[gatsby]/src/utils/api-runner-node.js:439:14

  • From previous event:

  • api-runner-node.js:431 resolve
    [gatsby-site]/[gatsby]/src/utils/api-runner-node.js:431:13

  • From previous event:

  • api-runner-node.js:347 module.exports
    [gatsby-site]/[gatsby]/src/utils/api-runner-node.js:347:3

  • source-nodes.ts:94 _default
    [gatsby-site]/[gatsby]/src/utils/source-nodes.ts:94:9

  • source-nodes.ts:24 sourceNodes
    [gatsby-site]/[gatsby]/src/services/source-nodes.ts:24:9

  • interpreter.js:720 Interpreter.exec
    [gatsby-site]/[xstate]/lib/interpreter.js:720:27

  • interpreter.js:223 Interpreter.execute
    [gatsby-site]/[xstate]/lib/interpreter.js:223:22

  • interpreter.js:243 Interpreter.update
    [gatsby-site]/[xstate]/lib/interpreter.js:243:18

  • interpreter.js:144
    [gatsby-site]/[xstate]/lib/interpreter.js:144:23

  • scheduler.js:59 Scheduler.process
    [gatsby-site]/[xstate]/lib/scheduler.js:59:13

  • scheduler.js:43 Scheduler.schedule
    [gatsby-site]/[xstate]/lib/scheduler.js:43:14

  • interpreter.js:140 Interpreter.send
    [gatsby-site]/[xstate]/lib/interpreter.js:140:29

  • interpreter.js:838
    [gatsby-site]/[xstate]/lib/interpreter.js:838:23

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.