Google Form Search and Pull Spreadsheet Data

user_12732980 0
Viewed 1511

Hi I have google spreadsheet that contains customers' ID and their shipping status. I want to create google form where customers are able to input each of their own ID, with the return that the google form shows their shipping status.

I tried to look for solutions in internet but there was no luck. I am not really good in programming, i hope there is answer to this problem without having me to do some hard programming.

The sample case can be seen in here: https://docs.google.com/spreadsheets/d/14vSAeZxEJTzbNLLYEiref6qt-CMqiVi8alheLcIBugM/edit?usp=sharing

Google form should show something a little bit like is shown in cell D1:E3. Where customers can fill the form with their own customer id, and the google form shows the status.

google-forms google-sheets search
1 Answers

Consideration

There is no way to respond back in a Google Form directly. You can't show custom validation messages after From submission either.

Proposed solution

What about using email addresses additionally to the customerID to retrieve the shipping status? In this way you can easily build a notification system that will send an email if a matching customer ID is found in your spreadsheet.

To build such system you will have to build a Form with a Trigger.

It is required a bit of programming but I will try to cover the most important parts the best I can:

Adapt your Database structure

Add the customers email addresses in the column C in order to be able to retrieve it using the same customer ID.


|  A |   B    |   C   |
|----+--------+-------|
| ID | STATUS | EMAIL |

Build the Form Trigger

In the Form you are using click on the 3 dots from the top menu and select Script Editor. Here you will write the code that will power your notification system.

function installTrigger() {
 // This function instructs the program to trigger the checkID function whenever a form response is submitted
 ScriptApp.newTrigger('checkID')
   .forForm(FormApp.getActiveForm())
   .onFormSubmit()
   .create();
}

function checkID(e) {
  // This function will parse the response to use the customer ID to retrieve email address and shipping status from the Spreadsheet Database
  var responses = e.response.getItemResponses(); // Gets the form responses
  var id = responses[0].getResponse(); // Assuming the first answer (index 0) is the customer ID)

  var found = SpreadsheetApp.openById('spreadsheet_id')
     .getRange('Sheet1!A1:C8') // The spreadsheet cells range in A1 Notation
     .getValues() // Retrieve their values in rows
     .filter((row) => row[0] == id); // Filter the rows for the provided customer ID

  if (found) {
    var status = found[0][1]; //Column B
    var email = found[0][2]; //Column C
    var subject = "Shipping Status";
    var message =
        `Hello!
          
         The status of the order number ${id} is: ${status}.`
    MailApp.sendEmail(email, subject, message);
  }
}

Install the trigger

From the Script Editor top menu run the installTrigger() function: Run>Run function>installTrigger.

You are done

Following these steps you have successfully set up the notification system. Now you can start sharing the Form link and accept responses.

References

Installable Triggers

Mail App

user_74536560
Related