Author: Ashish Singh Chauhan
Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google. The service also includes: Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites and Google Keep.
The app allows users to create and edit files online while collaborating with other users in real-time. Edits are tracked by users with a revision history presenting changes.
Google Sheets is available as a web application, mobile app for: Android, iOS, Microsoft Windows, BlackBerry OS and as a desktop application on Google’s Chrome OS. The app is compatible with Microsoft Excel file formats.
Google Sheets is available as a web application supported on: Google Chrome, Microsoft Edge, Firefox, Internet Explorer, and Safari web browsers. Users can access all spreadsheets, among other files, collectively through the Google Drive website.
Step 1– Enable Google Sheets Api from Google cloud console.
- Visit – https://console.cloud.google.com/ and login with your account.
- Create a New project.
- Now Select this project and search for Google Sheets Api and enable it.
- Top Left corner navigation → “API’s & Services” → oAuth Consent Screen.
- Configure the OAuth Consent Screen, click on the “OAuth Consent Screen” and select “External” and then click “Create”, this defines the type of application. If you select “Internal” additional verification checks would be required and would have to submit your app for verification.
- Next, configure your app and add the spreadsheet scope by clicking on “Add Scope” as Scopes: Click on add scope → and add the “../auth/spreadsheets” scope
- Now go to Credentials and create credentials in OAuth 2.0 Client Id’s → Select application type as “Web Application” → Add Authorized Redirect Url as http://localhost:8081/oauth2callback → Save. Make sure to copy the client id and client secret.
Step 2 → Integration with Mulesoft.
- Create mule application → Search in exchange → Google sheets connector → Finish
- Do the configuration for Google sheets connector.
- Consumer key and Consumer secret is your client ID and client secret respectively which you save in Step 1 (6th part).
Step 3 → Spreadsheet Operations
- Get spreadsheet properties – This will return the properties of the spreadsheet whose ID you passed.
- When running the application, you need to establish oAuth dance. To this, hit url – “http://localhost:8081/authorize” in the browser and allow access to retrieve the token.
- Get spreadsheet properties look like this →
- In the postman request pass the spreadsheet ID.
- Get Spreadsheet Values → Get the values of your sheet.
- The sheet will look like →
- Get spreadsheet values look like →
- Spreadsheet: Your spreadsheet ID
- Range: Your sheet name
- Postman request like this →
- Append Spreadsheet Values – Appending the values in your sheet.
- Append spreadsheet values look like –
- Range: From which row you want to append.
- Input data option: Operation to perform.
- Postman request like →
- Value is appended into sheet →
For More operations and information please refer to the References section of this document.
REFERENCES
- Google Sheet Api’s: https://developers.google.com/sheets/api/reference/rest
- Google Sheet connector reference : https://www.mulesoft.com/exchange/com.mulesoft.connectors/mule4-google-sheets-connector/