How to create a form with Google sheets + Apps script
Let’s suppose you have this database:
You want to add data, but with a form to maintain the structure. There are several options:
- Google form
- Typeform
- Google sheets
Did you say Google sheets? How to make a form in Google sheets?
Let’s see how to build it.
Quick overview of the final product
Let’s see how to build the form in Sheets and then the code.
1. Mapping the fields
The idea is to be able to map all the fields and intersperse them with empty cells so that they are well formatted in the form.
Step 1: Concatenate all the headers with “,,”
=TEXTJOIN(“, ,”,TRUE,’Data ✋’!A1:1)
Step 1 Output:
Step 2: Split by “,” to leave an empty cell between each header
=SPLIT(E4,”,”)
Step 2 Output:
Step 3: Transpose the step 2
=TRANSPOSE(E6:Q6)
Step 3 Output:
All together:
=TRANSPOSE(
SPLIT(
TEXTJOIN(“, ,”,TRUE,’Data ✋’!A1:G1)
,”,”)
)
Final output:
This way, if we add more columns to the DB, they will be added to the form automatically.
Note: The next id is generated with the MAX formula.Obtaining the highest number of IDs and summing 1
2. Create the helper tab
The next trick is to create a transition tab where the form data will appear transposed with this formula on cell A1:
=TRANSPOSE(QUERY(‘Form 👉’!B4:C16,”SELECT * WHERE C IS NOT NULL”))
Then all our code has to do is:
1. Take all the data from “Form data for append 👉” +
2. Paste it in the next row of the database (“Data ✋”) and
3. Clean the form
3. Build the code
4. Add Submit button
Once we have the code and the form, the only thing left to do is to add a submit button to make it really look like a form.
I generally use Figma to design my buttons but you can use Google Sheets directly with the draw function.
5. Assign the function with the submit button
In Google sheets, we can add an Apps script function to an image (button) to run the function by clicking on it.
E voilà! we have our form finished and ready to use:
Looking for the code? Here is the template
Looking for other G Suite workflow solutions? Check out the template gallery
Need help automating your spreadsheet? Get in touch
