Customer Order Form in Google Sheet and Apps Script

How to create an Customer Order Form in Google Sheets and Apps Script?

In this post, we will learn how to develop Customer Order Form with conditional data transfer. With the help of this form, you can take order form customer and transfer the data on product sheet.

Suppose, you have 5 products and you are getting order for Product 1 then data would be transferred to Product 1 sheet only. Same goes for other products as well.

As Google Sheet is available on cloud, you can run this form from any platform without any compatibility issues.

Customer Order Form in Google Sheet and Google Apps Script

Please follow the below steps to develop the Customer Order Form in Google Sheet and Apps Script.

  1. Login to Google Drive with your existing Google ID (1) or create a new Google Account (3) and then login to Google Drive.
Login to Google Drive with existing ID or create an account to login

2. Click on New and then click on Folder to create a new folder for our project.

3. In new folder window, just enter ‘Customer Order Form‘ and then click on Okay.

Create a new folder for data entry project

4. Now open the Customer Order Form folder to create Google Sheet in it.

5. Right click in Customer Order Form folder and then select Google Sheets -> Blank spreadsheet from the pop-up menu.

Creating Google Sheet

Once you will click on Blank spreadsheet, it will create and open blank spreadsheet in new tab of the browser.

New spreadsheet

6. Rename the spreadsheet with ‘Customer Order Form’.

Rename the blank spreadsheet

7. Create and rename five worksheets in the newly created spreadsheet as ‘Form‘, ‘Mobile‘, ‘Tablet‘, ‘Laptop‘, and ‘TV‘.

Add five sheets in this spreadsheet

We will utilize ‘Form’ sheet to create a data entry form and ‘Mobile’, “Tablet”, “Laptop” and “TV” sheets to store the data transferred from data entry form.

8. Let’s move to the Mobile sheet and create the required column headers in row 1 starting from Column A to H e.g. Customer Name, Mobile Number, Country, Product, Quantity, Remarks, Submitted On and Submitted By.

9. Create the same column headers in ‘Tablet‘, ‘Laptop‘, and ‘TV‘ sheets.

Column headers

10. Let’s move to the ‘Form’ sheet and start designing the Customer Order Form with required labels, input fields and buttons.

Data Entry form designing

Here, we have today date in cell E4, Customer Name (cell C7), Mobile Number (cell C9), Country ( cell C11), Product (cell C13), Quantity (cell C15) and Remarks ( cell C17).

These are the main input fields which will be updated by user to transfer the data from this from the respective Product sheet. In these input fields, we will utilize Country and Product as a drop-down.

11. To create a drop-down for Country, just click on Data menu and then select Data Validation.

Creating drop-down fields

12. In Data Validation window, select ‘List of items’ from the drop-down of Criteria and enter India,Japan,Spain,France,Brazil in text box.

Tick the ‘Show dropdown list in cell’ and ‘Show validation help text:’. Enter the help text in given text box as ‘Please select Country from the drop-down.’ Now click on Save to create the drop-down for Country.

Country drop-down

13. Repeat the step 11 to create the drop-down for Product. In Data Validation window, select ‘List of items’ from the drop-down of Criteria and enter Mobile,Tablet,Laptop,TV in text box.

Tick the ‘Show dropdown list in cell’ and ‘Show validation help text:’. Enter the help text in given text box as ‘Please select Product from the drop-down.’ Now click on Save to create the drop-down for Product.

Product drop-down

14. To create a button, just click on Insert menu and then click on Drawing.

Drawing window

15. In Drawing window, just draw a 3D rectangle and fill with desired color and give the caption as Submit. After creating the button, click on Save and Close to insert the button on sheet.

Creating Submit Button

16. Follow the step 14 to open the Drawing window.

In Drawing window, just draw a 3D rectangle and fill with desired color and give the caption as Reset. After creating the button, click on Save and Close to insert the button on sheet.

Creating Reset Button

Now, we have done with designing the form, creating drop-downs and inserting the required buttons.

Form

Let’s move to the code window.

17. To open the Apps Script window, just click on Tools menu and then click on Script Editors.

Tools menu

Once you click on Script editor in Tools menu, it will open the Apps Script window for Customer Order Form project.

18. Click on ‘Untitled project’ to rename it to ‘Customer Order Form’.

Rename the project

19. Remove myFunction from the Code.gs window and write a function to transfer the data from Form to Products sheet on selection. We will assign this function on click event of Submit button available on Form sheet.

20. Create a function to reset the form. We will utilize this function on click event of Reset button available on Form sheet.

Now, we have done with writing the functions which are required to Reset the form and transfer the data from Form to Product sheets. Let’s assign all these function to the respective buttons available in Form sheet.

21. Let’s right click on Submit button available on Form sheet and then click on ellipsis (triple dots) available on the right side of button and then click on ‘Assign script’.

22. In ‘Assign script’ window, just enter the function name and then click on OK button.

Assigning function to button

23. Repeat the step 21 and 22 to assign resetForm function on Reset button.

So, we are done with developing an automated Customer Order Form in Google Sheet and Apps Script to transfer the data conditionally.

Please watch the YouTube tutorial to develop this automated from from scratch.

You can follow the below like to open the User form and see the coding.

https://docs.google.com/spreadsheets/d/1YYxGn6KShpT_vfg7UgLVboqIob_ims6zVLzBzAyPtOc/edit?usp=sharing

Download the Google Apps Script code.

Download
Click to download

Please share your feedback and question in comment section. Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here