how can i connect this email input to google sheets ?

i used chatgpt to connect this with google sheets but it didn't work chatgpt made me create private key in google cloud console but it's not working is there any other way ?
77 Replies
peterpumkineaterr
i want to take email address as an input and store that email in google sheets
clevermissfox
clevermissfox11mo ago
You will need a script on your google sheet and to connect that endpoint to your form so it populates into the sheet on submit
peterpumkineaterr
DEV Community
Connecting your HTML forms to a Google spreadsheet
If you would prefer a video check it out on youtube at . HTML forms are one part of the frontend...
peterpumkineaterr
i followed this now my code looks like this am i doin something wrong ?
clevermissfox
clevermissfox11mo ago
I can’t look at the article right now but did you link the correct script URL so your form knows where to go? And when adding your script did you make sure to set permissions correctly?
peterpumkineaterr
well script URL is right but don't know bout permissions how can i do that ? i think the java script that i used is not really connected with the button and input field
clevermissfox
clevermissfox11mo ago
Permissions when you deploy the script with the deploy button
peterpumkineaterr
oh yeah that is done can i show you the script ?
clevermissfox
clevermissfox11mo ago
What’s going on when you hit the submit button? Are you getting an alert saying Success or Error?
peterpumkineaterr
nothing this is the script do you think something wrong here with email and notify button
clevermissfox
clevermissfox11mo ago
I’ve never seen querySelector submit. Maybe it should be button[type=submit]? What happens if you console log submitButton
peterpumkineaterr
where ?
clevermissfox
clevermissfox11mo ago
In the browser where you have the page with the form open
peterpumkineaterr
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
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()
}
}
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
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()
}
}
i only want to receive email but it has something date return header === 'Date' ? new Date() : e.parameter[header]
clevermissfox
clevermissfox11mo ago
First you need to make sure you’re selecting the element you have the event listener. On
peterpumkineaterr
how can i do. that i mostly do front end scss max not even js so kinda confused here
clevermissfox
clevermissfox11mo ago
In your js file at the bottom, do a console.log(submitButton) Then open your dev tools and go to the console See what it returns I’m new to js so probably not the best person to help you with this but we can try to debug a little bit
peterpumkineaterr
is this right ? cool
clevermissfox
clevermissfox11mo ago
Yes now open the console in the dev tools
peterpumkineaterr
sorry but where is that ?
clevermissfox
clevermissfox11mo ago
Okay let’s do this a different way On your html where you have the button add an id <button type=“submit” id=“submitButton”>notify me.</button> then in your script const submitButton = querySelector(‘#submitButton’)
peterpumkineaterr
done
clevermissfox
clevermissfox11mo ago
You changed your querySelector to #submitButton ?
peterpumkineaterr
yes
clevermissfox
clevermissfox11mo ago
Now try the form
peterpumkineaterr
no
clevermissfox
clevermissfox11mo ago
At the very least you should get an alert with success or error
peterpumkineaterr
no alert
clevermissfox
clevermissfox11mo ago
What do you get when you copy the script url and paste it into a new tab and navigate to it
clevermissfox
clevermissfox11mo ago
Then the script URL is incorrect
peterpumkineaterr
how ?
clevermissfox
clevermissfox11mo ago
Idk but it says right there function not found You need the correct url to the script you set up in your google sheets script When you deployed did you set permissions correctly?
peterpumkineaterr
oh god yes exactly the same as it shown in tutorial
clevermissfox
clevermissfox11mo ago
The url is the endpoint, it needs to be able to connect your file to that script I would watch a few tutorials to see if things have changed since that one was posted a couple years ago
peterpumkineaterr
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
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()
}
}
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
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()
}
}
is anything wrong in this script ?
clevermissfox
clevermissfox11mo ago
I couldn’t tell you And I got to go, sorry to leave you without a solution but keep working on it , do some googling and use your dev tools!! Google Dev Tools , you get there by right clicking and choosing inspect. The console will be your best friend
peterpumkineaterr
thanks for this help man i'll see what i can do next
clevermissfox
clevermissfox11mo ago
Omg You closed off your form twice You have ``<form></form> <input…> <button></button> </form>
peterpumkineaterr
what how what should i do ?
clevermissfox
clevermissfox11mo ago
You need
<form>
<input…>
<button></button>
</form>
<form>
<input…>
<button></button>
</form>
The input and button need to be inside the form tag
peterpumkineaterr
let's see got the new working url too when i click submit i got this
clevermissfox
clevermissfox11mo ago
I haven’t watched the tutorial you did but see if anything is different from this one, I have it saved as I used it before and it worked https://youtu.be/jdIntB1J-i8?si=xkQZ4N46dB3HpENK
Learning Axis
YouTube
Google Sheets | How To Send HTML Form Data To Google Sheets
#html #css #javascript #tutorials #trending #new #google #googlesheets #integration Source code (HTML Form): https://shorturl.at/frDFY App Scripts: https://shorturl.at/cklT2 Sound effects from Pixabay.
peterpumkineaterr
i added this in the script function doGet(e) { return HtmlService.createHtmlOutput('This is your web app'); } did everything same not working
clevermissfox
clevermissfox11mo ago
You made sure to click “run” once you put the script into google sheets ? And when deploying set your permissions to Anyone ? Copied the endpoint after deploying and put that into the script url ?
peterpumkineaterr
yes
clevermissfox
clevermissfox11mo ago
Try putting it into your form inline under “action” and comment out everything you have on file (const form= etc) That’ll help you identify where the issue is
peterpumkineaterr
if i only keep it for action then nothing happens i think i know what is wrong link is giving same error
clevermissfox
clevermissfox11mo ago
What do you think is wrong ? Then it’s with the sheets side. Are you absolutely sure you set your permissions on Deploy to “anyone”? And that you copied the correct endpoint?
peterpumkineaterr
he is using input a submit button i thought that is wrong
clevermissfox
clevermissfox11mo ago
No it shouldn’t matter they both have type=submit
peterpumkineaterr
i'm sure and i can bet anything on that
clevermissfox
clevermissfox11mo ago
There’s something wrong on the sheets side because that 405 error means the google sheets side received the request but can’t deliver, often because the url is wrong. Can you show me the scriptURL you’re using
clevermissfox
clevermissfox11mo ago
It still says function not found. Try going to a brand new sheet, do all your naming, make a new Apps Script, run it, deploy it and get that new url
peterpumkineaterr
i used the same script ok wait
clevermissfox
clevermissfox11mo ago
Make sure the Sheet Name is correct And make sure you’re copying the endpoint from the deploy and not the address bar Hmm so nowhere in the script is there a function doGet() it’s called doPost. But you added a function doGet(){….} in the script on Sheets ?
peterpumkineaterr
script is not opening on other account
clevermissfox
clevermissfox11mo ago
What do you mean not opening ? You have to run Apps Script right ?
peterpumkineaterr
app script is not opening bad request 400
clevermissfox
clevermissfox11mo ago
Maybe they are using Christmas to do some maintenance or something
peterpumkineaterr
i guess so
clevermissfox
clevermissfox11mo ago
Could be the issue you’re having because it sounds like everything is right
peterpumkineaterr
which name should i give here ?
clevermissfox
clevermissfox11mo ago
Doesn’t the tutorial tell you? I think it’s the name you gave the sheet Sheet1 I believe is the default Check if it has a space
peterpumkineaterr
well he used different name than actual sheet name should i add triggers ?
clevermissfox
clevermissfox11mo ago
Yeah it’s the same name as your spreadsheet Triggers? Idk where are you seeing that ? Honestly I bet it’s the service is down for maintenance right now if you connected everything correctly Not the whole sheet, the little tab at the bottom where you make new sheets . On the screenshot I sent it says form data. Whatever that name is is what goes into the variable const sheetName. Copy it exactly
peterpumkineaterr
FUCK it worked damnnn but it gave me one problem ui is messed up it suppose to look like this
clevermissfox
clevermissfox11mo ago
Just add some padding to the form. Yay! What was the issue, did you identify what you did differently? Probably because of how you had that extra form closing tag while you were writing the css, nothing to do with the script . That’s a much easier problem to fix 🤣
peterpumkineaterr
i thik it was sheet name
clevermissfox
clevermissfox11mo ago
Oh my goodness well I’m very glad it works now
peterpumkineaterr
yeah i'm relived and finally i can deploy how to give padding to form ? i'm unable to make this text center
clevermissfox
clevermissfox11mo ago
On your form css padding:1em; Center between what and what ? You could try text-align:center; on your button styles and see if that’s what you’re looking for
peterpumkineaterr
oh i figured it out. i had to give display flex to form in scss if you're available can you take a look at my post it's very weird. https://discord.com/channels/436251713830125568/1189185573630525450
clevermissfox
clevermissfox11mo ago
As it’s a background image you’ll need to make the div it lives in overflow the page to be pushed outside the viewport and have overflow hidden set on the body or html.
peterpumkineaterr
The website is good now. Not perfect in responsiveness but it’s okay
clevermissfox
clevermissfox11mo ago
Inputs are one of the only elements that aren’t responsive out of the box. It’s in a flex container so you should be able to give it a width:100% or so it’s more responsive You could also create a media query with flex-wrap so the form wraps at a certain breakpoint.
Want results from more Discord servers?
Add your server