brandonmp / gatsby-source-google-sheets Goto Github PK

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

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!

this happened a lot as of a few months ago (last time i used gatsby).

simply wrapping the fetch in something like async-retry should be sufficient

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?

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?

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'

I have an excel sheet with korean and chinese columns, it seems it is not parsing it. Is there any plans to support unicode in the future?

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?

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

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.

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?

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.

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

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

I am using google sheet as a Data source for the data available to my Gatsby site. Everything is working pretty well except the email column. I am getting email column value as 0 instead of actual email.

Could someone let me know some info about this.

Is there a way to allow storing of google api credentials within .env variables?

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 ⏱

I'd like to be able to pull from two worksheets. How can I accomplish this?

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!

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.

The tutorial link in Step 1 is broken and results in a 404:

Follow this tutorial: https://www.twilio.com/blog/2017/03/google-spreadsheets-and-javascriptnode-js.html

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" } }

Pretty self-explanatory. Just making a note for anyone else debugging. It thinks it's at the end of the sheet because of a single empty row.

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).

Google is phasing out their old v3 api which this plugin depends on via the google-spreadsheet plugin. I recently got an email from Google saying

On January 26, 2021, the Sheets v3 API will be completely shut down.

So this is quite urgent. Will the dependency on the google-spreadsheet plugin be updated soon?

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

@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?

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.

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.

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'

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!

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.

Hello ✌️

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