brandonmp / gatsby-source-google-sheets Goto Github PK
View Code? Open in Web Editor NEWA GatsbyJS plugin that pulls nodes from rows in a Google Sheet.
A GatsbyJS plugin that pulls nodes from rows in a Google Sheet.
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 all! I know this Error is highlighted at the bottom of the README
under Troubleshooting.
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!
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?
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?
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!
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:
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?
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.
I'd like to be able to pull from two worksheets. How can I accomplish this?
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'
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.
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.
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
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
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.
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?
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
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!
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.
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.
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?
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
Is there a way to allow storing of google api credentials within .env variables?
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" } }
@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?
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 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 โฑ
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?
Steps to reproduce:
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.
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.