Makanju OluwafemiOne thing that Google does that amazes me is the use cases that come with working with their workspace tools, be it Gmail, Google, or Meet. There are different ways to work with them.
In this article, we will explore integrating Google Sheets, a workspace tool created to serve as an online spreadsheet. It's a feature-rich text editor where you can create, edit and collaborate. How do we intend to do this? Imagine we are making a waitlist for a start-up product and need to get people's data and store it for a campaign. You can leverage the Google Sheets API to get this data instead of worrying yourself about the problem of creating a backend and some database.
For you to follow this guide, honestly, I will say your experience in React or Vue won't matter, as basic Javascript knowledge can be applied to both. However, I will use a React app.
Introduction to AppScript
Appscript is a JavaScript cloud-based platform that allows you to integrate with Google products, You can automate tasks across products like calendars, spreadsheets, and so on. Our focus in this article is Appscript for Google Sheets.
Create and set up a Spreadsheet
Go to spreadsheet on Google and create a new blank spreadsheet.
Click on Extension on the spreadsheet editor tab, you will be presented with a pop-up that looks like this.
Click on Appscript, and you will be redirected to a new web-based editor to start writing your script.
Add a script and deploy your app.
Next, copy and paste the code below into your editor.
Next
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()
function initialSetup () {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
const lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
const sheet = doc.getSheetByName(sheetName)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
Next, click on the run button; you should get a success message in the output section.
To deploy these changes, click on the deploy button; a small modal will appear underneath it. choose a new deployment.
After that, a modal will pop up, Click select type settings and choose web app from the modal, then set "who has access" to anyone and "execute as" to me. Click on deploy; you should be redirected to the authorize screen. Follow the onscreen guide, and you should be okay.
A screenshot of how the modal should look.
When you are authorizing, Google might not have verified your app, so click advance to the unsafe site when authorizing; no issues.
When all steps have been completed, you should get a screen containing your deployment ID and Web App URL, Copy and save them somewhere because you will need them to be able to push from your React app to this sheet. Click done and you should be redirected to your web base editor.
Set Up A trigger
To set up a trigger, hover on the right pane; a swipe-out navigation layer will be presented, select Triggers.
Click on the add trigger button. You will get a modal popup for adding your trigger function. These functions are divided into four; you can edit whichever one you want to use for this guide.
Select do Post for the function to run, Head for deployment to run, from spreadsheet for the selective event source, and on form submit for the selective event type.
This is the image of how the triger modal should look.
Create a React Form and push data from your app
To create a form, I have bootstrapped a react and typescript code. As I have said, you can always apply everything here in another framework; nothing too complicated.
Next, copy this code into App.tsx, it's just an example to show how to implement it in your own code. Please take note that you have to pass your app ID to the base URL; also, your payload data needs to be passed to formData() for it to work.
App.tsx
import React from 'react';
import './App.css'
function App() {
const handlePost = async (event: React.FormEvent<HTMLFormElement>) => {
event.preventDefault();
const target = event.target as typeof event.target & {
email: {value: string},
}
const date = new Date()
const inputValue: {[key:string]:string} = {
'Email': target.email.value,
'Created At': date.toLocaleString(),
}
console.log(inputValue)
const APP_ID = 'APP_ID'
const baseURL = `https://script.google.com/macros/s/${APP_ID}/exec`
const formData = new FormData()
Object.keys(inputValue).forEach((key) => {
formData.append(key, inputValue[key])
})
try {
const res = await fetch(baseURL, {
method: 'POST',
body: formData,
})
if(res.ok){
console.log('Request was successful:', res);
}else{
console.log('Request Failed:', res);
}
}catch(e){
console.error('Error during fetch:', e);
}
}
return (
<div>
<form onSubmit={handlePost}>
<input type="text" name='email' placeholder='Enter Email'/>
<button>submit</button>
</form>
</div>
)
}
export default App
In this code example, I created a form to get user emails and send them to the sheet when the user clicks on submit. When a user clicks, an event handlePost is triggered, passed through an asynchronous action, and it sends a request containing the input email address and the current time and date.
If everything is successful, you should have your email and time in your Google sheet like this.
A screenshot of the spreadsheet with inputted data from the form
Conclusion
There are so many advantages to using Google Sheets, as it can be used in place of a backend to gather quick information from users from your app. There are other google workspace tool integration to explore, however, this will be our focus for now, hopefully later i can share articles on how to implement with other tools. Happy coding!
