Is there any way to match the html one field data with the Google sheet column. And check the second column after that and then enter the data

Question

I have a google sheet, and I want to get HTML form data in that google sheet. The google sheet has 4 columns.

1. Voucher Code
2. Name
3. Email
4. Phone number

Voucher code column is already filled with voucher codes.

And in the HTML form, person will fill the voucher code, name, email and Phone number. Voucher code will match the voucher code column and data of name, email and phone number will be filled in the same row. Now here I have to check the validation. If the voucher code, already has name, email and phone number. Then there should give a error message that already someone is there on that voucher code. And if the name, email and phone number column is empty for any voucher code. Then only the html form data should go.

Please help me regarding this.

Thank you

Answer 1

So if you want the front-end (the person filling out the form in their browser) to communicate with the back-end (your code and database) then it is best you the documentation. Based on your requirements, you will be sending a response back to the client after they have filled out the form. So in your form handler function, in the back-end you want to do all the checking and then provide a response.

Here is also an example with forms and client server communication.

So a simple example is

function formHandler(e){
  const voucherIdFromForm = e.voucherId

  const vouchers = SpreadsheetApp.getActive().getSheetByName("Vouchers").getRange("A2:A").getValues().flat()

 // lets look into the array and see if the voucher is new
 if( vouchers.indexOf( voucherIdFromForm ) == -1 ){
   // here you can get add in the name to the corresponding voucher
   return true
 } else {
   // Already exists
   return false
 }
}