DataMateApps - Simplify Your Data Management

Welcome to DataMate! This guide covers using DataMate for data management, form building, and contact management in Google Sheets. Current date: April 13, 2025.

DataMate is a Google Sheets add-on for streamlined data management and form creation. Key features include:

Automated Data Entry: Organize data across multiple sheets.
Record Management: Save, update, and view records.
Custom Forms: Build forms with 29 field types, including tables, checkout fields, hyperlinks, and media (images/videos).
Contact Management: Import and manage Gmail/CSV contacts.
Dynamic Logs: Track up to 12 log fields per record.
Email Notifications: Send automated emails on form submission with response details.

Watch the DataMate Overview Video

Installation

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

Initialize Your Spreadsheet

New Spreadsheet: Select "DataMate > FormBuilder > Preview Form" to create the FormSetup sheet.
Existing Spreadsheet: Ensure a FormSetup sheet exists or run "Preview Form" to generate it.

Adding Data

Use forms configured in FormSetup to input data, targeting sheets and cells defined in columns B:G.

Managing Records

Save: Form submissions store data in target sheets (e.g., Responses).
Custom Actions: List functions in FormSetup!B6 (e.g., save, newContactit) to run after submission.

Using the FormBuilder Interface

The HTML FormBuilder provides a visual editor for designing forms:

Launch: Go to "DataMate > FormBuilder > Form Builder" to open a 1200x600px modal.
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.
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").
Save: Click "Save" to update FormSetup!A9:J.
Test: Use "DataMate > FormBuilder > Preview Form" to see the form in action.

Manually edit FormSetup for advanced tweaks.

Configure Settings

In FormSetup:

B2: Form Name (e.g., "Customer Feedback Form").
B6: On Submit Functions (e.g., save, copyInput1, newContactit) - 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 A9: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:

Text

Single-line text input.

I: Blank
Example: ["Name", "Responses", "A", "Sheet2", "B2", "", "", "Text", "", "Yes"]

Dropdown

Single-choice dropdown.

I: Comma-separated options or range
Example: ["Dropdown", "Responses", "I", "", "", "", "", "Dropdown", "=Sheet1!A:A", "Yes"]

MultiSelect

Multi-choice dropdown.

I: Comma-separated options
Example: ["MultiSelect", "Responses", "J", "", "", "", "", "MultiSelect", "Red,Green,Blue", "No"]

Date

Date picker.

I: Blank
Example: ["Date", "Responses", "C", "Records", "B1", "", "", "Date", "", "No"]

Time

Time picker.

I: Blank
Example: ["Time", "Responses", "D", "", "", "", "", "Time", "", "No"]

Number

Numeric input.

I: Blank
Example: ["Number", "Responses", "E", "", "", "", "", "Number", "", "Yes"]

Checkbox

True/false checkbox.

I: Blank
Example: ["Checkbox", "Responses", "F", "", "", "", "", "Checkbox", "", "No"]

Radio

Single-choice radio buttons.

I: Comma-separated options
Example: ["Radio", "Responses", "G", "", "", "", "", "Radio", "Yes,No,Maybe", "Yes"]

Textarea

Multi-line text.

I: Blank
Example: ["Textarea", "Responses", "H", "", "", "", "", "Textarea", "", "No"]

Email

Email input with validation.

I: Blank
Example: ["Email", "Responses", "B", "", "", "", "", "Email", "", "Yes"]

StarRating

5-star rating.

I: Blank
Example: ["StarRating", "Responses", "K", "", "", "", "", "StarRating", "", "No"]

RangeSlider

Slider for numeric range.

I: "min,max,step"
Example: ["RangeSlider", "Responses", "L", "", "", "", "", "RangeSlider", "0,100,5", "No"]

FileUpload

File upload (max 6MB).

I: Blank
Behavior: Stores Google Drive link
Example: ["FileUpload", "Responses", "M", "", "", "", "", "FileUpload", "", "No"]

Conditional

Shows based on another field’s value.

I: "FieldName=Value"
Example: ["Conditional", "Responses", "N", "", "", "", "", "Conditional", "Checkbox=true", "No"]

Calculated

Read-only calculated value.

I: "=Formula" (basic arithmetic)
Example: ["Calculated", "Responses", "O", "", "", "", "", "Calculated", "=Number*2", "No"]

Signature

Canvas for drawing signatures.

I: Blank
Behavior: Saves as PNG link
Example: ["Signature", "Responses", "P", "", "", "", "", "Signature", "", "No"]

Geolocation

Captures latitude, longitude.

I: Blank
Behavior: Click "Get Location"
Example: ["Geolocation", "Responses", "Q", "", "", "", "", "Geolocation", "", "No"]

ProgressBar

Static progress bar (0-100).

I: Number
Example: ["ProgressBar", "", "", "", "", "", "", "ProgressBar", "75", "No"]

Captcha

Fixed "3 + 5" verification.

I: Blank
Behavior: Enter "8"
Example: ["Captcha", "Responses", "R", "", "", "", "", "Captcha", "", "Yes"]

Image

Static image display.

I: URL
Example: ["Image", "", "", "", "", "", "", "Image", "https://drive.google.com/uc?export=view&id=165kqv1atBk1WBbSkIbj6pnoikR9JOpLj", "No"]

Video

Playable video.

I: YouTube/MP4 URL
Example: ["Video", "", "", "", "", "", "", "Video", "https://www.youtube.com/watch?v=dQw4w9WgXcQ", "No"]

ImageLink

Input URL with preview.

I: Blank
Example: ["ImageLink", "Responses", "S", "", "", "", "", "ImageLink", "", "No"]

VideoLink

Input video URL.

I: Blank
Example: ["VideoLink", "Responses", "T", "", "", "", "", "VideoLink", "", "No"]

StaticText

Static text display.

I: Text
Example: ["StaticText", "", "", "", "", "", "", "StaticText", "This is static text", "No"]

Table

Displays a sheet range with media.

I: Range (e.g., "Sheet1!A1:F10")
Behavior: Renders URLs as images (Google Drive) or videos (YouTube)
Example: ["Table", "", "", "", "", "", "", "Table", "Sheet1!A1:F10", "No"]

Sample Sheet1:

A	B	C	D	E	F
Item	Price	Image	Video	Description	Stock
Widget	10	https://drive.google.com/uc?id=1A2B3C	https://youtu.be/dQw4w9WgXcQ	Small widget	100

Checkout

Table for selecting items with quantities, unit prices, and totals (includes tax).

I: Range (e.g., "Sheet1!A2:B10") with Description and Price columns
Behavior: Users select items, set quantities; displays subtotal, tax, and total
Output: Saves as JSON (e.g., [{"description":"Widget","quantity":2,"unitPrice":10}])
Example: ["Checkout", "Orders", "A", "", "", "", "", "Checkout", "Sheet1!A2:B10", "Yes"]

Sample Sheet1:

A	B
Description	Price
Widget	10
Gadget	20

Hyperlink

Displays a clickable link with custom text.

I: URL
Behavior: Renders as a styled link; no user input
Example: ["Hyperlink", "", "", "", "", "", "", "Hyperlink", "https://datamateapp.github.io/Donate%205%20per%20mo.html", "No"]

Container

Groups fields with styling.

I: Inline CSS
Example: ["Container", "", "", "", "", "", "", "Container", "border: 2px dashed #4CAF50;", "No"]

Header

Styled header with HTML support.

I: Text, CSS, or HTML
Example: ["Form Header", "", "", "", "", "", "", "Header", "Customer Feedback Form ", "No"]

Footer

Styled footer with HTML support.

I: Text, CSS, or HTML
Example: ["Form Footer", "", "", "", "", "", "", "Footer", "Thank you for your input! ", "No"]

Preview & Deploy

Test with "DataMate > FormBuilder > Preview Form". Deploy as a web app via "Deploy > New Deployment > Web App."

Collect Data

Share the web app URL. Data saves to sheets per B:G. Functions in B6 run post-submission. If B8 is set, an email with response details is sent.

Using Forms for Contacts

Target NewContact in B:G and add newContactit to B6 to save contacts.

Example: ["Email", "NewContact", "A", "", "", "", "", "Email", "", "Yes"]

Order Form Template

Paste into FormSetup!A9:J, create an Items sheet with items and prices, set B8 for notifications, then preview form:

A	B	C	H	I	J
Header			Header	<h2>Order Form</h2>	No
Name	Responses	A	Text		Yes
Email	Responses	B	Email		Yes
Order	Responses	C	Checkout	Items!A2:B10	Yes
Support			Hyperlink	<a href="https://datamateapp.github.io">Visit DataMate</a>	No
Footer			Footer	<p>Thanks for ordering!</p>	No

Customer Feedback Form Template

Paste into FormSetup!A9:J, create a Sheet1 with data for Dropdown, Table, and Checkout fields, set B2 to "Customer Feedback Form", B6 to "save, copyInput1, newContactit", and B8 for notifications, then preview form:

A	B	C	D	E	H	I	J
Form Header					Header	<h3 style='color: #4CAF50;'>Customer Feedback Form</h3>	No
Name	Responses	A	Sheet2	B2	Text		Yes
Email	Responses	B			Email		Yes
Date	Responses	C	Records	B1	Date		No
Time	Responses	D			Time		No
Number	Responses	E			Number		Yes
Checkbox	Responses	F			Checkbox		No
Radio	Responses	G			Radio	Yes,No,Maybe	Yes
Textarea	Responses	H			Textarea		No
Dropdown	Responses	I			Dropdown	=Sheet1!A:A	Yes
MultiSelect	Responses	J			MultiSelect	Red,Green,Blue	No
StarRating	Responses	K			StarRating		No
RangeSlider	Responses	L			RangeSlider	0,100,5	No
FileUpload	Responses	M			FileUpload		No
Conditional	Responses	N			Conditional	Checkbox=true	No
Calculated	Responses	O			Calculated	=Number*2	No
Signature	Responses	P			Signature		No
Geolocation	Responses	Q			Geolocation		No
Captcha	Responses	R			Captcha		Yes
ImageLink	Responses	S			ImageLink		No
VideoLink	Responses	T			VideoLink		No
StaticText					StaticText	This is static text	No
Table					Table	Sheet1!A1:F10	No
Container					Container	border: 2px dashed #4CAF50;	No
Checkout	Orders	A			Checkout	Sheet1!A2:B10	Yes
Hyperlink					Hyperlink	https://datamateapp.github.io/Donate%205%20per%20mo.html	No
ProgressBar					ProgressBar	75	No
Image					Image	https://drive.google.com/uc?export=view&id=165kqv1atBk1WBbSkIbj6pnoikR9JOpLj	No
Video					Video	https://www.youtube.com/watch?v=dQw4w9WgXcQ	No
Form Footer					Footer	<p style='font-style: italic;'>Thank you for your input!</p>	No

Visit:

DataMate Tutorial

1. Overview

2. Getting Started