Node.js write data in a Google Sheet
Tags: node, database
Published: 2020-09-08 05:50:44

In this series, we are using Google sheets as a database.
Yesterday we had a look at [reading data from a Google sheet](https://daily-dev-tips.com/posts/nodejs-reading-a-google-sheet-%F0%9F%A4%93/).
Today we are going to take a step further and actually write data to the sheet.

We will be using the same script to begin with.
So if your looking for the starting explanation on authentication, visit the article on [reading google sheet in node.js](https://daily-dev-tips.com/posts/nodejs-reading-a-google-sheet-%F0%9F%A4%93/).

Today's exercise is going to work like this:

![Node Google sheet insert](https://cdn.hashnode.com/res/hashnode/image/upload/v1599394414740/GKVvc-N0a.gif)

## Node.js write data to Google sheet

First of all, we had our initial app setup to only being able to write so we need to give it new permissions:

Change 

```js
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
```

To reflect the whole of Google sheets API's

```js
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
```

> Note: We can't use Google's own sheet, so copy the sheet to your own version.

If we already had a `token.json`, remove this and rerun the `node .` command to get a new one.

Now let's change the action we do once we read the `credentials.json` file.
We used to call the `listMajors` function, so now we are going to change that to be `writeData`

The function will now look like this:

```js
fs.readFile('credentials.json', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    authorize(JSON.parse(content), writeData);
});
```

Great stuff, we can now go ahead and create this `writeData` function

```js
function writeData(auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    let values = [
        [
            'Chris',
            'Male',
            '1. Freshman',
            'FL',
            'Art',
            'Baseball'
        ],
        // Potential next row
    ];
    const resource = {
        values,
    };
    sheets.spreadsheets.values.append({
        spreadsheetId: '1XnbJ5JHeJS2KsTzz6wXtwASb5mkwkICn_XP_pDJIyTA',
        range: 'Class Data!A1',
        valueInputOption: 'RAW',
        resource: resource,
    }, (err, result) => {
        if (err) {
            // Handle error
            console.log(err);
        } else {
            console.log('%d cells updated on range: %s', result.data.updates.updatedCells, result.data.updates.updatedRange);
        }
    });
}
```

We start by defining our new sheets API object and pass it our authentication.
Then we define our "new" object. We have to convert this into an Object for the API to accept it.
Then we call the Sheets API and use the `append` method.

For this endpoint, we are passing four items:

- spreadsheetId: Your unique spreadsheet id, you can find this in the URL
- range: For this example, we are using the A1 row. It will automatically append the new data at the first available row.
- valueInputOption: This can be used to pass formula. But we use 'RAW' data.
- resource: The actual new object.

We then get an error or result object, in our case we console.log both of them.
As the result you get a full object, stating which rows have been affected by this query.

That's it. We can now append data to our Google sheet!

You can find this version on [GitHub](https://github.com/rebelchris/node-google-sheet/tree/insert).

### Thank you for reading, and let's connect!

Thank you for reading my blog. Feel free to subscribe to my email newsletter and connect on [Facebook](https://www.facebook.com/DailyDevTipsBlog) or [Twitter](https://twitter.com/DailyDevTips1)