I want to read and write Google Sheets with React to update the data for an app I built with Glideapps, a no-code app builder that uses Google Sheets as the data source.
It took me quite a while to figure out how to do that. But turns out the solution is quite straight-forward.
TL;DR:
- Generate a Google service account key file, get
private_keyandclient_emailvalues.- Get your
spreadsheet_idandsheet_id.- Share your spreadsheet with editing permission with the email address in your
client_emailfield.- Make sure the 1st row in your spreadsheet contains the header fields. These values will be the "keys" in your data objects.
- Install google-spreadsheet and use it to connect to Google Sheets. There are very good examples in the docs here, and I have included a snippet of how to append to the spreadsheet at the bottom of this post.
Here are the detailed step-by-step instructions with screenshots:
1) Go to Google's developer console:
https://console.developers.google.com/
Click on "Select a project" then click on "New project", give it a name.
Click on "Credentials" then click on "Manage service accounts", then click on "Create service account".
Add a name and description, then click "Create"
Click "Continue" on the "Service account permissions" page, as it is optional.
Click "Done" on "Grant users access to this service account" page, as it is also optional.
Click "Create key" under the Actions column.
Select the default "JSON" and Google will download a key file to your default download folder.
If you open the JSON file, there are two fields you'll need: "private_key" and "client_email"
2) Go to your Google Sheets and find the spreadsheet_id and sheet_id
3) IMPORTANT Remember to share your spreadsheet with the email address in your key file with the key client_email
You should see the "person added" message right away.
4) Make sure your 1st row contains the column names
5) There are very good examples on Googe-Spreadsheet for reading and writing to the Spreadsheet.
Here is a snippet for appending a new row to the spreadsheet I wrote:
import { GoogleSpreadsheet } from "google-spreadsheet";
// Config variables
const SPREADSHEET_ID = process.env.REACT_APP_SPREADSHEET_ID;
const SHEET_ID = process.env.REACT_APP_SHEET_ID;
const CLIENT_EMAIL = process.env.REACT_APP_GOOGLE_CLIENT_EMAIL;
const PRIVATE_KEY = process.env.REACT_APP_GOOGLE_SERVICE_PRIVATE_KEY;
const doc = new GoogleSpreadsheet(SPREADSHEET_ID);
const appendSpreadsheet = async (row) => {
try {
await doc.useServiceAccountAuth({
client_email: CLIENT_EMAIL,
private_key: PRIVATE_KEY,
});
// loads document properties and worksheets
await doc.loadInfo();
const sheet = doc.sheetsById[SHEET_ID];
const result = await sheet.addRow(row);
} catch (e) {
console.error('Error: ', e);
}
};
const newRow = { Name: "new name", Value: "new value" };
appendSpreadsheet(newRow);
Top comments (27)
Y'all guys gonna come to an issue,
error - ./node_modules/google-auth-library/build/src/auth/googleauth.js:17:0Module not found: Can't resolve 'child_process'
null
You must do this inside your next.config.js to make everything work
ignore the other settings and focus on the webpack part.
Magic! Thanks man! :)
Where do we find next.config.js file, I can't seem to find that file
Hi guys,
If you having some issues with the permission, Just go to google console and Enable the google sheets API.
Also, while using your private key, add this piece of code
process.env.PRIVATE_KEY.replace(/\n/g, '\n')
Hi, it always said that my API key is not valid. Do we need to remove the -----BEGIN PRIVATE KEY-----\n part of the API key? I guess yes?
thank you 👍
I can't seem to resolve this error, I'm currently using @5.0.0, I'm new to develop, hope to get some help
Compiled with problems:X
ERROR in ./node_modules/buffer-equal-constant-time/index.js 4:13-37
Module not found: Error: Can't resolve 'buffer' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/buffer-equal-constant-time'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "buffer": require.resolve("buffer/") }'
- install 'buffer'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "buffer": false }
ERROR in ./node_modules/gaxios/build/src/gaxios.js 27:16-32
Module not found: Error: Can't resolve 'https' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gaxios/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "https": require.resolve("https-browserify") }'
- install 'https-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "https": false }
ERROR in ./node_modules/gaxios/build/src/gaxios.js 31:38-60
Module not found: Error: Can't resolve 'querystring' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gaxios/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "querystring": require.resolve("querystring-es3") }'
- install 'querystring-es3'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "querystring": false }
ERROR in ./node_modules/gaxios/build/src/gaxios.js 35:14-28
Module not found: Error: Can't resolve 'url' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gaxios/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "url": require.resolve("url/") }'
- install 'url'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "url": false }
ERROR in ./node_modules/google-auth-library/build/src/auth/googleauth.js 20:24-48
Module not found: Error: Can't resolve 'child_process' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
ERROR in ./node_modules/google-auth-library/build/src/auth/googleauth.js 22:11-24
Module not found: Error: Can't resolve 'fs' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
ERROR in ./node_modules/google-auth-library/build/src/auth/googleauth.js 26:11-24
Module not found: Error: Can't resolve 'os' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "os": require.resolve("os-browserify/browser") }'
- install 'os-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "os": false }
ERROR in ./node_modules/google-auth-library/build/src/auth/googleauth.js 28:13-28
Module not found: Error: Can't resolve 'path' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "path": require.resolve("path-browserify") }'
- install 'path-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "path": false }
ERROR in ./node_modules/google-auth-library/build/src/auth/oauth2client.js 20:20-42
Module not found: Error: Can't resolve 'querystring' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "querystring": require.resolve("querystring-es3") }'
- install 'querystring-es3'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "querystring": false }
ERROR in ./node_modules/google-auth-library/build/src/auth/oauth2client.js 22:15-32
Module not found: Error: Can't resolve 'stream' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/auth'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "stream": require.resolve("stream-browserify") }'
- install 'stream-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "stream": false }
ERROR in ./node_modules/google-auth-library/build/src/crypto/node/crypto.js 20:15-32
Module not found: Error: Can't resolve 'crypto' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-auth-library/build/src/crypto/node'
Did you mean './crypto'?
Requests that should resolve in the current directory need to start with './'.
Requests that start with a name are treated as module requests and resolve within module directories (node_modules, /Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules).
If changing the source code is not an option there is also a resolve options called 'preferRelative' which tries to resolve these kind of requests in the current directory too.
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "crypto": require.resolve("crypto-browserify") }'
- install 'crypto-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "crypto": false }
ERROR in ./node_modules/google-p12-pem/build/src/index.js 14:11-24
Module not found: Error: Can't resolve 'fs' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-p12-pem/build/src'
ERROR in ./node_modules/google-p12-pem/build/src/index.js 18:15-30
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/google-p12-pem/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/gtoken/build/src/index.js 14:11-24
Module not found: Error: Can't resolve 'fs' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gtoken/build/src'
ERROR in ./node_modules/gtoken/build/src/index.js 20:13-28
Module not found: Error: Can't resolve 'path' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gtoken/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "path": require.resolve("path-browserify") }'
- install 'path-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "path": false }
ERROR in ./node_modules/gtoken/build/src/index.js 22:15-30
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/gtoken/build/src'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/https-proxy-agent/dist/agent.js 45:30-44
Module not found: Error: Can't resolve 'net' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/https-proxy-agent/dist'
ERROR in ./node_modules/https-proxy-agent/dist/agent.js 47:30-44
Module not found: Error: Can't resolve 'tls' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/https-proxy-agent/dist'
ERROR in ./node_modules/https-proxy-agent/dist/agent.js 49:30-44
Module not found: Error: Can't resolve 'url' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/https-proxy-agent/dist'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "url": require.resolve("url/") }'
- install 'url'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "url": false }
ERROR in ./node_modules/https-proxy-agent/dist/agent.js 51:33-50
Module not found: Error: Can't resolve 'assert' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/https-proxy-agent/dist'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "assert": require.resolve("assert/") }'
- install 'assert'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "assert": false }
ERROR in ./node_modules/jwa/index.js 5:13-30
Module not found: Error: Can't resolve 'crypto' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jwa'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "crypto": require.resolve("crypto-browserify") }'
- install 'crypto-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "crypto": false }
ERROR in ./node_modules/jwa/index.js 9:11-26
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jwa'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/jws/lib/data-stream.js 4:13-30
Module not found: Error: Can't resolve 'stream' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "stream": require.resolve("stream-browserify") }'
- install 'stream-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "stream": false }
ERROR in ./node_modules/jws/lib/data-stream.js 6:11-26
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/jws/lib/sign-stream.js 8:13-30
Module not found: Error: Can't resolve 'stream' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "stream": require.resolve("stream-browserify") }'
- install 'stream-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "stream": false }
ERROR in ./node_modules/jws/lib/sign-stream.js 12:11-26
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/jws/lib/tostring.js 2:13-37
Module not found: Error: Can't resolve 'buffer' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "buffer": require.resolve("buffer/") }'
- install 'buffer'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "buffer": false }
ERROR in ./node_modules/jws/lib/verify-stream.js 8:13-30
Module not found: Error: Can't resolve 'stream' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "stream": require.resolve("stream-browserify") }'
- install 'stream-browserify'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "stream": false }
ERROR in ./node_modules/jws/lib/verify-stream.js 12:11-26
Module not found: Error: Can't resolve 'util' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/jws/lib'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "util": require.resolve("util/") }'
- install 'util'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "util": false }
ERROR in ./node_modules/safe-buffer/index.js 2:13-30
Module not found: Error: Can't resolve 'buffer' in '/Users/nguyenminhduc/Desktop/workspace/moneyTrack/money-track/node_modules/safe-buffer'
BREAKING CHANGE: webpack < 5 used to include polyfills for node.js core modules by default.
This is no longer the case. Verify if you need this module and configure a polyfill for it.
If you want to include a polyfill, you need to:
- add a fallback 'resolve.fallback: { "buffer": require.resolve("buffer/") }'
- install 'buffer'
If you don't want to include a polyfill, you can use an empty module like this:
resolve.fallback: { "buffer": false }
Same error here.
ditto error here in react18
I also had this issue... did anyone figure out how to solve it?
Thanks for the screenshots with arrows
Great article, thanks for sharing!
Is it possible to make this a bit more safe, ie restrict reading / updating of data with some cors restrictions? Or whatever other suggestion you might have, it's welcome
One more thing, how does re-authentication work in this case? Do credentials expire and if they do how are they renewed?
Common Problems I faced
1 . If You're Using Next JS , add this to your next.config.js
2 . Do not remove
----BEGIN PRIVATE KEY----or----END PRIVATE KEY----or anything else from your private key.3 . Do this to your private key before using
process.env.GOOGLE_SERVICE_PRIVATE_KEY.replace(/\\n/g, '\n')4 . Enable Google Sheet API from google console
Nice post. A question: do I have to Publish the spreadheet on the Web in order for google-spreadsheet to be able to access it?
You only need to share the spreadsheet with your google app. ^_^
:(
I'm having the same problem this user describes on StackOverflow. Apparently he was able to fix it, but his solution didn't work for me. Would you have any clue of what may be going on? stackoverflow.com/questions/644108...
Hi Joao,
Try this,
process.env.PRIVATE_KEY.replace(/\n/g, '\n')
Also enable the google sheets api in google console.
Hope it works for you !
Thank you!
Top notch Stuff!!
This is very helpful. Thank you!
Can we use this with react native?
Is it safe to use the spreadsheet credentials in the .env?
Someone can steal the emails no ?
just add the .env to your gitignore that way it doesn't publish the .env itself and the creds stay safe
Can anyone tell me if i can still use this approach using this kind of google spreedsheet format? and if yes can anyone tell me how???
Can anyone tell me if i can still use this approach using this kind of google spreedsheet format? and if yes can anyone tell me how?
hi can anyone tell me if i can use this approach using this kind of excel format?
ive been having hard time by this task