Google Sheets API and Node.Js
Google provides API to automate it’s spreadsheet. We can read, write, update, etc. data from the spreadsheet using Google Sheets API. Various languages like Java, Go, .Net, etc supports the API but here we are going to use Node.js for the automation.
NOTE:- We need little knowledge of Javascript, so I hope that you are familiar with it.
First, we start off by installing Node JS in our code editor by using the command:-
npm init
with an entry point as main.js or anything which we would like to use. Google provides an API that helps us to interact with Google sheets. It helps us to modify any aspect of the sheet (like writing sheets, modifying it etc).
Now following the first step we need to install The Google Api in our folder. So in order to do that we have the command:-
npm install googleapis@39 — — save (it’s double hyphne save)
This very command can be found on Google Spreadsheets Api website and link to the same is:-
Now follow these steps:-
- Create a file with the name of your entry point. Here it’s main.js.
- Import the googleapi module by using:-
const {google}=require(‘googleapis’);
3. One more thing which we require is a google console which is basically used to give us some keys, auth values so as to connect to the APi. So that can be found on:-
4. Click on create project. A window will open after project creation. Now click on enable Api’s. Another window will open up showing various API’s available. If now, we can also search for Google Sheets Api.
5. Click on Google Sheets API>Enable.
6. Now we need some credentials. So click on create credentials. A page will open up with a drop-down menu. Click on Google Sheets API (inside the column which API you are using.)
7. On the same page, we will be asked about “from where we’ll be calling the API”. Select the Node.js server from the dropdown menu followed by application data from the below option box which is further followed by “no I’m using others” and hit the blue “why do I need credentials” button.
8. A new page will open asking us for the credentials:-
· Inside service account, give any name. It’s basically the account through which we give permissions to the api.
· Beside the service account, a column of “Select Role” will be given. Select Project>Editor option. The editor role is only if we read the info. For writing etc we need to change it to owner etc.
· Key Type should be JSON.
· Click on continue. A pop-up will show up and a file will be downloaded.
9. Once the JSON file is downloaded, move it to the same file as our project. This JSON file is necessary for security. So don’t share it with anyone. We can rename the file as its name is too long. So we rename it to “Keys.json”.
10. Coming back to main.js file, we need to import the keys.json to our main.js. So we:-
const keys=require(‘./keys.json’);
11. Now we need to create a variable which we can use to receive tokens, so:-
const client=new google.auth.JWT(keys.client_email, null,keys.private_key,[‘https://www.googleapis.com/auth/spreadsheets']);
· Client is the variable name.
· Google.auth.JWT() is used to provide authorization for all our actions. It takes in certain parameters. Hovering the cursor over () will help to see the params.
o The first one is the client email. It’s already provided inside the JSON which we downloaded and we just need to access it via the above “keys.client_email” command. Here “keys” is the module which we imported above.
o The second parameter will remain null.
o We need a private key
o Now the last parameter takes in the scope. Google Api’s have many scopes available which can be seen via the link:- https://developers.google.com/identity/protocols/oauth2/scopes#sheets
o As we need to write and read the sheet, so we select the above scope (given in the pic). Various scopes have many different links.
12. Now we need to create the connection, so we use:-
client.authorize(function(error,tokens){
if(error){console.log(error);return;}
else
{console.log(‘connected…’);}
});
Here, error will be thrown and the program will not work further.
13. Now, we have to run our file. So, save the file and inside the terminal type:-
node filename (here node main)
if everything works fine, we’ll get “connected….” as the output.
14. Now, we can work on the Sheet as per our need, and in order to do so, we can create a separate function that can have less callbacks. And to do so we can have an async function as:-
async function gsrun(cl){ //cl for client
const gsapi=google.sheets({version:’v4', auth:cl });
const opt={
spreadsheetId: ‘1KUeIDm-lfw9jLT5jP0vURJNjOKeUiNqVtpj5_bJLvBE’,
range:’Sheet1!A1:B5'
};
let dataObatined= await gsapi.spreadsheets.values.get(opt);
console.log(dataObtained.data.values);
}
· We have an async function that takes in our client(from above) as parameters.
· We need to assign the version and auth for our client, hence we do so in the next step and assign it inside a variable so that we can perform functions on it.
· Now we need to specify the target Spreadsheet and target range. Hence, we create a constant variable with fields id and range.
NOTE- We should not change the range and spreadsheetId names.
Ø To get the spreadsheet Id, head onto Google spreadsheet creation page. Create a new spreadsheet and from the URL, extract the Id between / and /. It’ll be an alphanumeric set of characters as shown above.
Ø Now fill the spreadsheet with some data. Here we use ID and Name as the column name. Carefully, see the range in which we have filled the values. For example, we used the range A1 to B5. You can use whatever you feel like, provided it’s filled.
The Range here takes the spreadsheet name (SHEET1 here) and the range (A1 to B5) as filled.
NOTE:- Carefully, see the name of your spreadsheet, else it’ll throw an error. The default name of every sheet is SHEET1.
· Now, we take a variable (dataObatined) and assign it the sheeted, range in order to extract the data. As we are using 3rd party API, hence we don’t know how much time the API calls take. Hence we use await keyword which further runs the program only when API calls are made.
To use the await keyword, we used async function.
· Now, we just need to print the data. So the data which comes in JSON form inside the dataObatined variable, we extract data and values from there. Hence dataObatined.data.values.
· Lastly, don’t forget to call this async function from the else of client.authorize by issuing gsrun(client) command.
15. Now, suppose we need to add hyphen to data or add another field to the received data, so we need to perform the following actions:-
let dataArray=dataObtained.data.values;
let valueNew=dataArray.map(function(r){
r.push(r[0]+’-’+r[1]);
return r;
});
console.log(valueNew);
So, dataArray is just the filed from above used to shorten the “.” Usage. Rest is just simply JavaScript code where we take a new variable use the map function on it to pass the values of dataArray and make a new field out of it and assign it to the valuenew variable.
log() will just give the value of the change we made.
WRITING THE DATA ONTO THE SPREADSHEET
16. The data which we have altered above can be written back to the spreadsheet inside particular columns that we want. In order to do so, we need to prepare an authentication token as we did with opt above and we name it updateoptions here.
const updateoptions={
spreadsheetId: ‘1KUeIDm-lfw9jLT5jP0vURJNjOKeUiNqVtpj5_bJLvBE’,
range:’Sheet1!E2',
valueInputOption: ‘USER_ENTERED’,
resource:{values: valueNew}
};
· The First line shows the spreadsheetId. We can write in any spreadsheet provided we have the ID and req. permissions.
· Second is the range that holds our data. Here, we need to just specify the starting point and the ending cell will be figured out by the API itself.
· The Third line is the valueInputOptions which need to be as it is.
· Fourth line is the resource that we need to write onto the sheet. Here, valueNew is the array of (Updated from point 14) data that we need to write.
17. Next, we need to make a call to the API which is done via:-
let resp=await gsapi.spreadsheets.values.update(updateoptions);
console.log(resp);
Here, gsapi is the variable from above point 14 which is used to update the values given in updateoptions. On running the node filename command, the values will be updated onto the sheet and the status code will be printed onto the console.
So, that’s pretty much it. The last thing to note, that in case the user has left any cell blank then the API automatically fills it as undefined in the JSON and if we write a data onto the sheet, then it’ll be a whitespace on the sheet.
18. And in case we want to convert the above code into express.js code and call it using get request, then we need to add the following code:-
app.get(‘/response’,async function(req,res){
var dataRecieved= await gsrun(client);
res.send(dataRecieved);
})
app.listen(3000);
where app is the express module import:-
const exp=require(‘express’);
const app=exp();
We also need to enable JSON parsing in express, so:-
const bodyParser=require(‘body-parser’)
app.use(bodyParser.json())
And that it. We can switch to localhost:3000/response to see the sheet data onto the homepage!
Thanks for reading!
