How to input data into Google Sheets using PHP + JavaScript
Years ago I wrote on how to populate a Google Sheet from a NativeScript app. I needed to do the same thing from my web application and there were a few things that changed with Google Docs.
Step 1: Set up the Google Sheet
Enter the column headers. It’ll be populated from code, so I avoided having a space.
Optional: Rename the Google Doc from “Untitled project” to something useful. Here, I did “Sample from web app”.
Tip: “freeze” the top row so you can always see the column headers when scrolling. Go to View >> Freeze >> 1 Row
Open up the Google Docs code by going to Extensions >> App Script
Upon doing so, you’ll see a default myFunction()
Replace that with the code below:
Optional: rename it from “Untitled project” to something more useful
Save the Project
After you save it, there will be a dropdown with available functions:
Change the function to “setup”.
Then run the “setup” function:
You’ll see the “execution log” appear, but after a few seconds you’ll get a prompt about authorization:
Hit “Review permissions” and choose your Google account. You’ll get a scary prompt about it not being verified:
Hit the “Advanced” link. Then you’ll see:
At the bottom left is another link that says “Go to [your app name] (unsafe)”. Click on that.
On the next screen, hit button for “Allow”.
You’ll see the execution log completed:
Hit the “Deploy” button and select “New Deployment”
You’ll see this screen:
Next to “Select type,” click the gear icon. Then click “Web app”.
Fill out the form:
- Description: anything
- Execute as: Me
- Who has access: Anyone
Hit “Deploy” button.
You’ll be prompted to authorize access:
Again you’ll have to re-specify:
- Choose your Google Account (same as before)
- Click “Advanced”
- Click link for “Go to [your app name] (unsafe)”.
- Allow
Tip: If you did not set it to “Anyone” then you won’t get this additional authorization check, and the final app will not work.
Eventually you’ll get a successful confirmation with a Deployment ID and a URL. Copy the URL.
Step 2: Set up the website
Let’s start with a simple HTML form:
Upon submitting the form, a JavaScript function is called:
This will submit the form contents to PHP:
Of course on row 9, use the URL with your Deployment ID.
The route I took was printing out the URL the user should be redirected to. If they missed something and encountered a server side error, they could potentially go somewhere like error.html. Otherwise they’ll go to thanks.html.
The JavaScript will detect the text sent back from the server and redirect the user accordingly.
It’ll insert data into the Google Sheets document:
One Major “Gotcha”
At one point I got the following error:
{“result”:”error”,”error”:{“name”:”Exception”}}
This happened because I wasn’t the original author; someone had shared the Google Sheet with me. When I made the document myself, it worked. Maybe there’s some sort of security precaution in place such that only the original author is able to successfully create a Deployment ID?
The full GitHub repository can be found here: https://github.com/stevesohcot/google-sheet-php-javascript
