Installation

Install DataMate from the Google Workspace Marketplace. Open a Google Sheet, and look for the "DataMate" menu.

Initialize Your Spreadsheet

• New Spreadsheet: Open a new Google Sheet, enter data in A3:Q48 on Sheet1, and select "DataMate > New Dataset" to set up specialized sheets (Input, Data, View_Print, Update, Log).
• Existing Spreadsheet: Ensure Sheet1 has data in A3:Q48, then run "New Dataset."

Adding Data

Enter data in Input sheet cells A3:Q48. Merge cells containing text for proper formatting.

Setting Up Logs

In the Input sheet:

• For each log (e.g., Log 1): Type =, select the label cell (e.g., "Invoice Number"), and press Enter.
• Below each log label, type =, select the data cell, and press Enter. Up to 12 logs are supported.

Managing Records

• Save: Click "DataMate > Save Record" to store data in the Data sheet and view it in View_Print.
• View: Use the dropdown in View_Print!B2 to select records.
• Update: Edit records in the Update sheet; changes sync to Data.
• Print: In View_Print, go to "File > Print > Current Selection."
• Reset: Use "Reset Input" or "Reset View/Print" to refresh sheets from Sheet1.

Using the FormBuilder Interface

Watch the Form Builder Video

The HTML FormBuilder provides a visual editor for designing forms:

1. Launch: Go to "DataMate > FormBuilder > Form Builder" to open a 1200x600px modal.
2. Interface:
   • Field Palette: Drag field types (e.g., Text, Checkout, Hyperlink, ImageLink) onto the canvas.
   • Properties Panel: Edit field name, targets (sheets/cells), type, options, and required status.
   • Preview: View the form layout as you build.
3. Build:
   • Drag a "Header" for the title (e.g., "Customer Feedback Form").
   • Add fields like "Email", "Dropdown", "StarRating", or "Table" (link to a sheet range).
   • Set properties (e.g., "Required: Yes", "Options: =Sheet1!A:A").
4. Save: Click "Save" to update FormSetup!A10:J.
5. Test: Use "DataMate > FormBuilder > Preview Form" to see the form in action.

Manually edit FormSetup for advanced tweaks.

Configure Settings

In FormSetup:

• I6: Form Name (e.g., "Customer Feedback Form").
• B6: On Submit Functions (e.g., checkout, save, copyInput1, newContact) - Runs after submission.
• B8: Notification Email (e.g., "your-email@example.com") - Sends an email with form response details on submission.

Add Fields

Define fields in A10:J (or via FormBuilder):

• A: Field Name
• B: Target Sheet 1
• C: Target Cell/Column 1 (e.g., A1 or A)
• D: Target Sheet 2
• E: Target Cell/Column 2
• F: Target Sheet 3
• G: Target Cell/Column 3
• H: Field Type
• I: Options (e.g., dropdown values, formulas, URLs, ranges)
• J: Required (Yes/No)

Supported Field Types

All 29 field types with examples:

Preview & Deploy

1. Preview Form
Navigate to DataMate > FormBuilder > Preview Form to review your form before deployment or use internally to protect formulas.

2. Deploy as a Web App (via Google Apps Script)
- Optional: Use Open Source Code
To open the Apps Script Editor click Extensions>Apps Script.
Delete existing code, copy the provided open-source code from our website and paste it into the Apps Script Editor.
- Steps to Deploy:
1. In the Apps Script Editor, click Deploy > New Deployment.
2. Select Web App.
3. Configure the deployment settings:
- Description: Enter a name, e.g., "DataMate FormBuilder".
- Execute as: Select "Me" (runs under your Google account).
- Who has access: Choose either:
- "Anyone" (public access).
- "Anyone with a Google account" (restricted to Google users).
4. Click Deploy.
5. Authorize Permissions:
- Click>Advanced then Go to Untitled project (unsafe).
- When prompted, review the permissions requested by the script (e.g., access to Google Drive).
- Sign in with your Google account if required.
- Click Allow to grant the necessary permissions for the web app to function.
6. Copy the generated Web App URL.

3. Share the Form
Share the Web App URL with users to access the form directly in their browsers.

Watch the DataMate Deploy as a web app Video

Collect Data

Share the URL. Data saves to the target sheet based on submission type. Dynamic table data is serialized as JSON in a single cell for TableRow submissions.

Setup Contact Sheets

Select "DataMate > AddressBlock > Add Contact Sheets" to create contacts, Address, and NewContact sheets.

Import Contacts

Use "Import Gmail™ Contacts" to upload a CSV file from Gmail or Outlook into contacts.

Watch the AddressBlock Video

Add New Contacts

Enter details in NewContact and save via "AddressBlock > Save New Contact."

Generate Address Blocks

In Address, set F1 to a cell reference, then use "AddressBlock > Address Block Name" or "Company" to populate contact data.

📧 New: Mail It Sidebar

Located in DataMate > AddressBlock > Mail It, this opens a sidebar email tool for sending messages to selected contacts. Features:

• Loads contact email and details from the “Address” or “contacts” sheet
• Supports Gmail-style sending with subject/body input
• Requires authorization to send emails from your account

Example custom html Headers and Footer

Paste these into FormSetup!column I:

Navigation Showcase Features Footer Contact or Sign-Up Forms Event Registrations Order or Booking Forms Quizzes and Assessments Surveys and Questionnaires

Job Application Form Template

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Job Application Form	No
Section 1: Personal Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	B					Text		Yes
Street	Responses	C					Text		Yes
City	Responses	D					Text		Yes
State	Responses	E					Text		Yes
ZIP	Responses	F					Text		Yes
Phone Number	Responses	G					Text		Yes
Email Address	Responses	H					Email		Yes
Are you at least 18 years old?	Responses	I					Radio	Yes,No	Yes
Section 2: Position Information							Container	background: #f0f0f0; padding: 10px;	No
Position Applying For	Responses	J					Dropdown	Developer,Designer,Manager	Yes
Desired Salary	Responses	K					Radio	$40,000-$50,000,$50,001-$60,000,$60,001+	Yes
Date Available to Start	Responses	L					Date		Yes
Full-time	Responses	M					Checkbox		No
Part-time	Responses	N					Checkbox		No
Weekends	Responses	O					Checkbox		No
Evenings	Responses	P					Checkbox		No
How did you hear about this position?	Responses	Q					Dropdown	Website,Referral,Job Board	Yes
Section 3: Employment Eligibility							Container	background: #f0f0f0; padding: 10px;	No
Are you legally authorized to work in this country?	Responses	R					Radio	Yes,No	Yes
Will you now or in the future require work sponsorship?	Responses	S					Radio	Yes,No	Yes
Section 4: Employment History							Container	background: #f0f0f0; padding: 10px;	No
Employer Name	Responses	T					Text		Yes
Job Title	Responses	U					Text		Yes
Start Date	Responses	V					Date		Yes
End Date	Responses	W					Date		Yes
Duties and Responsibilities	Responses	X					Textarea		Yes
Supervisor’s Name and Contact Info	Responses	Y					Text		Yes
May we contact this employer?	Responses	Z					Radio	Yes,No	Yes
Reason for Leaving	Responses	AA					Text		Yes
Section 5: Education							Container	background: #f0f0f0; padding: 10px;	No
Institution Name	Responses	AB					Text		Yes
Degree or Certification Earned	Responses	AC					Text		Yes
Field of Study	Responses	AD					Text		Yes
Graduation Date	Responses	AE					Date		Yes
Section 6: Skills and Qualifications							Container	background: #f0f0f0; padding: 10px;	No
List relevant skills	Responses	AF					Textarea		Yes
Certifications or Licenses	Responses	AG					Text		No
Section 7: References							Container	background: #f0f0f0; padding: 10px;	No
Reference Full Name	Responses	AH					Text		Yes
Relationship	Responses	AI					Text		Yes
Phone/Email	Responses	AJ					Text		Yes
Years Known	Responses	AK					Text		Yes
Section 8: Legal and Background							Container	background: #f0f0f0; padding: 10px;	No
Have you ever been convicted of a felony?	Responses	AL					Radio	Yes,No	Yes
If yes, please explain	Responses	AM					Conditional	Have you ever been convicted of a felony?=Yes	No
Do you consent to a background check?	Responses	AN					Radio	Yes,No	Yes
Section 9: Declaration							Container	background: #f0f0f0; padding: 10px;	No
I certify that the information provided is true and complete	Responses	AO					Checkbox		Yes
Signature	Responses	AP					Signature		Yes
Date	Responses	AQ					Date		Yes
Form Footer							Footer	Thank you for applying!	No

Note: Adjust column I as needed (e.g., customize dropdowns, add more history/reference sections).

Contact / Sign-Up Form

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Contact / Sign-Up Form	No
Section 1: Personal Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		Yes
Email Address	Responses	B					Email		Yes
Phone Number	Responses	C					Text		No
Section 2: Contact Preferences							Container	background: #f0f0f0; padding: 10px;	No
Preferred Contact Method	Responses	D					Dropdown	Email,Phone,SMS	Yes
Best Time to Contact	Responses	E					Radio	Morning,Afternoon,Evening	No
Subscribe to Newsletter	Responses	F					Checkbox		No
Section 3: Message							Container	background: #f0f0f0; padding: 10px;	No
Your Message	Responses	G					Textarea		Yes
How Did You Hear About Us?	Responses	H					Dropdown	Website,Referral,Social Media,Other	Yes
Section 4: Consent							Container	background: #f0f0f0; padding: 10px;	No
I consent to the collection and use of my information	Responses	I					Checkbox		Yes
Form Footer							Footer	Thank you for reaching out! We'll get back to you soon.	No

Event Registration Form

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Event Registration Form	No
Section 1: Personal Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		Yes
Email Address	Responses	B					Email		Yes
Phone Number	Responses	C					Text		No
Section 2: Event Details							Container	background: #f0f0f0; padding: 10px;	No
Event Selection	Responses	D					Dropdown	Workshop,Conference,Gala	Yes
Number of Attendees	Responses	E					Number		Yes
Preferred Session Time	Responses	F					Radio	Morning,Afternoon,Evening	No
Section 3: Additional Information							Container	background: #f0f0f0; padding: 10px;	No
Dietary Restrictions	Responses	G					Textarea		No
Special Accommodations	Responses	H					Textarea		No
How Did You Hear About This Event?	Responses	I					Dropdown	Website,Referral,Social Media,Other	Yes
Section 4: Payment and Consent							Container	background: #f0f0f0; padding: 10px;	No
Registration Fee	Responses	J					Checkout	Events!A2:B10	Yes
I agree to the event terms and conditions	Responses	K					Checkbox		Yes
Form Footer							Footer	Thank you for registering! You will receive a confirmation soon.	No

Order / Booking Form

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Order / Booking Form	No
Section 1: Customer Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		Yes
Email Address	Responses	B					Email		Yes
Phone Number	Responses	C					Text		Yes
Section 2: Booking Details							Container	background: #f0f0f0; padding: 10px;	No
Service/Product	Responses	D					Dropdown	Consultation,Repair,Subscription	Yes
Booking Date	Responses	E					Date		Yes
Preferred Time	Responses	F					Time		Yes
Quantity	Responses	G					Number		Yes
Section 3: Additional Information							Container	background: #f0f0f0; padding: 10px;	No
Special Requests	Responses	H					Textarea		No
How Did You Hear About Us?	Responses	I					Dropdown	Website,Referral,Social Media,Other	Yes
Section 4: Payment and Confirmation							Container	background: #f0f0f0; padding: 10px;	No
Order Total	Responses	J					Checkout	Products!A2:B10	Yes
I agree to the terms of service	Responses	K					Checkbox		Yes
Form Footer							Footer	Thank you for your order! We will confirm your booking soon.	No

Quiz / Assessment Form

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Quiz / Assessment Form	No
Section 1: Participant Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		Yes
Email Address	Responses	B					Email		Yes
Participant ID	Responses	C					Text		No
Section 2: Quiz Questions							Container	background: #f0f0f0; padding: 10px;	No
Question 1: What is the capital of France?	Responses	D					Radio	Paris,London,Berlin	Yes
Question 2: Rate your confidence in this topic	Responses	E					StarRating		Yes
Question 3: Describe a key concept	Responses	F					Textarea		Yes
Question 4: Select all applicable skills	Responses	G					MultiSelect	Problem Solving,Communication,Teamwork	Yes
Section 3: Feedback							Container	background: #f0f0f0; padding: 10px;	No
How challenging was this quiz?	Responses	H					RangeSlider	1,10,1	Yes
Additional Comments	Responses	I					Textarea		No
Section 4: Consent							Container	background: #f0f0f0; padding: 10px;	No
I certify that my responses are my own	Responses	J					Checkbox		Yes
Form Footer							Footer	Thank you for completing the quiz! Results will be shared soon.	No

Survey / Questionnaire Form

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	Survey / Questionnaire Form	No
Section 1: Respondent Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		No
Email Address	Responses	B					Email		No
Age Group	Responses	C					Dropdown	18-24,25-34,35-44,45-54,55+	Yes
Section 2: Survey Questions							Container	background: #f0f0f0; padding: 10px;	No
How satisfied are you with our service?	Responses	D					StarRating		Yes
How likely are you to recommend us?	Responses	E					RangeSlider	0,10,1	Yes
Which features do you use most?	Responses	F					MultiSelect	Mobile App,Website,Support,Other	Yes
What can we improve?	Responses	G					Textarea		No
Section 3: Additional Feedback							Container	background: #f0f0f0; padding: 10px;	No
Would you participate in future surveys?	Responses	H					Radio	Yes,No,Maybe	Yes
How did you hear about this survey?	Responses	I					Dropdown	Email,Website,Social Media,Other	Yes
Section 4: Consent							Container	background: #f0f0f0; padding: 10px;	No
I consent to my responses being used for analysis	Responses	J					Checkbox		Yes
Form Footer							Footer	Thank you for your feedback! Your responses help us improve.	No

12th Grade Math Final Exam

Paste this into FormSetup!A10:J:

A	B	C	D	E	F	G	H	I	J
Form Header							Header	12th Grade Math Final Exam	No
Section 1: Student Information							Container	background: #f0f0f0; padding: 10px;	No
Full Name	Responses	A					Text		Yes
Student ID	Responses	B					Text		Yes
Class Period	Responses	C					Dropdown	1st,2nd,3rd,4th,5th	Yes
Section 2: Algebra and Functions							Container	background: #f0f0f0; padding: 10px;	No
Question 1: Solve for x: 2x^2 - 8x + 6 = 0	Responses	D					MultiSelect	x=1,x=3,x=-1,x=-3	Yes
Question 2: Find the inverse of f(x) = 2x + 3	Responses	E					Radio	f^-1(x)=(x-3)/2,f^-1(x)=2x-3,f^-1(x)=(x+3)/2	Yes
Section 3: Calculus							Container	background: #f0f0f0; padding: 10px;	No
Question 3: What is the derivative of f(x) = e^(2x)?	Responses	F					Radio	2e^(2x),e^(2x),2e^x	Yes
Question 4: Evaluate the integral: ∫(0 to 1) 3x^2 dx	Responses	G					Number		Yes
Section 4: Trigonometry and Probability							Container	background: #f0f0f0; padding: 10px;	No
Question 5: Solve: cos(x) = -0.5 for 0 ≤ x ≤ 2π	Responses	H					MultiSelect	2π/3,4π/3,π/3	Yes
Question 6: What is the probability of rolling a 4 on a fair six-sided die?	Responses	I					Radio	1/6,1/4,1/2	Yes
Section 5: Open-Ended							Container	background: #f0f0f0; padding: 10px;	No
Question 7: Explain the process to find the derivative of a composite function	Responses	J					Textarea		Yes
Section 6: Feedback							Container	background: #f0f0f0; padding: 10px;	No
How challenging was this exam?	Responses	K					RangeSlider	1,5,1	Yes
Additional Comments	Responses	L					Textarea		No
Section 7: Certification							Container	background: #f0f0f0; padding: 10px;	No
I certify that I completed this exam independently	Responses	M					Checkbox		Yes
Form Footer							Footer	Thank you for completing the final exam! Your teacher will review your responses.	No