Kevin Powell - Community•13mo ago

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

peterpumkineaterrOP•13mo ago

i want to take email address as an input and store that email in google sheets

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

https://dev.to/kromate/connecting-your-html-forms-to-a-google-spreadsheet-p9h

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...

peterpumkineaterrOP•13mo ago

i followed this now my code looks like this am i doin something wrong ?

clevermissfox•13mo 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?

peterpumkineaterrOP•13mo ago

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•13mo ago

Permissions when you deploy the script with the deploy button

peterpumkineaterrOP•13mo ago

oh yeah that is done can i show you the script ?

clevermissfox•13mo ago

What’s going on when you hit the submit button? Are you getting an alert saying Success or Error?

peterpumkineaterrOP•13mo ago

nothing this is the script do you think something wrong here with email and notify button

clevermissfox•13mo ago

I’ve never seen querySelector submit. Maybe it should be button[type=submit]? What happens if you console log submitButton

peterpumkineaterrOP•13mo ago

where ?

clevermissfox•13mo ago

In the browser where you have the page with the form open

peterpumkineaterrOP•13mo ago

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•13mo ago

First you need to make sure you’re selecting the element you have the event listener. On

peterpumkineaterrOP•13mo ago

how can i do. that i mostly do front end scss max not even js so kinda confused here

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

is this right ? cool

clevermissfox•13mo ago

Yes now open the console in the dev tools

peterpumkineaterrOP•13mo ago

sorry but where is that ?

clevermissfox•13mo 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’)

peterpumkineaterrOP•13mo ago

done

clevermissfox•13mo ago

You changed your querySelector to #submitButton ?

peterpumkineaterrOP•13mo ago

yes

clevermissfox•13mo ago

Now try the form

peterpumkineaterrOP•13mo ago

clevermissfox•13mo ago

At the very least you should get an alert with success or error

peterpumkineaterrOP•13mo ago

no alert

clevermissfox•13mo ago

What do you get when you copy the script url and paste it into a new tab and navigate to it

peterpumkineaterrOP•13mo ago

clevermissfox•13mo ago

Then the script URL is incorrect

peterpumkineaterrOP•13mo ago

how ?

clevermissfox•13mo 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?

peterpumkineaterrOP•13mo ago

oh god yes exactly the same as it shown in tutorial

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

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•13mo 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

peterpumkineaterrOP•13mo ago

thanks for this help man i'll see what i can do next

clevermissfox•13mo ago

Omg You closed off your form twice You have

``<form></form> 
    <input…>
   <button></button>
</form>

peterpumkineaterrOP•13mo ago

what how what should i do ?

clevermissfox•13mo ago

You need

<form>
    <input…>
   <button></button>
</form>

<form>
    <input…>
   <button></button>
</form>

The input and button need to be inside the form tag

peterpumkineaterrOP•13mo ago

let's see got the new working url too when i click submit i got this

clevermissfox•13mo 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.

peterpumkineaterrOP•13mo ago

i added this in the script

function doGet(e) {
  return HtmlService.createHtmlOutput('This is your web app');
}

did everything same not working

clevermissfox•13mo 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 ?

peterpumkineaterrOP•13mo ago

yes

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

if i only keep it for action then nothing happens i think i know what is wrong link is giving same error

clevermissfox•13mo 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?

peterpumkineaterrOP•13mo ago

he is using input a submit button i thought that is wrong

clevermissfox•13mo ago

No it shouldn’t matter they both have type=submit

peterpumkineaterrOP•13mo ago

i'm sure and i can bet anything on that

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

sure https://script.google.com/macros/s/AKfycby2Q_W3eae5cJJwtQHjb5ckBQnzqvVupCFYY2uLQFeok1b_R2RdPuGNc3drejIyfK95EA/exec

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

i used the same script ok wait

clevermissfox•13mo 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 ?

peterpumkineaterrOP•13mo ago

script is not opening on other account

clevermissfox•13mo ago

What do you mean not opening ? You have to run Apps Script right ?

peterpumkineaterrOP•13mo ago

app script is not opening bad request 400

clevermissfox•13mo ago

Maybe they are using Christmas to do some maintenance or something

peterpumkineaterrOP•13mo ago

i guess so

clevermissfox•13mo ago

Could be the issue you’re having because it sounds like everything is right

peterpumkineaterrOP•13mo ago

which name should i give here ?

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

well he used different name than actual sheet name should i add triggers ?

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

FUCK it worked damnnn but it gave me one problem ui is messed up it suppose to look like this

clevermissfox•13mo 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 🤣

peterpumkineaterrOP•13mo ago

i thik it was sheet name

clevermissfox•13mo ago

Oh my goodness well I’m very glad it works now

peterpumkineaterrOP•13mo ago

yeah i'm relived and finally i can deploy how to give padding to form ? i'm unable to make this text center

clevermissfox•13mo 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

peterpumkineaterrOP•13mo ago

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•13mo 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.

peterpumkineaterrOP•13mo ago

The website is good now. Not perfect in responsiveness but it’s okay

clevermissfox•13mo 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.

Gaming

Programming

how can i connect this email input to google sheets ?