Make a custom template to create a starting point for new spreadsheets
- Tweet
- Share
What to Know
- Make a spreadsheet with the data and formatting for a template. In a new folder, click New >Google Sheets to make an empty Sheet.
- In the source spreadsheet, highlight and copy the content you want to use in the template. Paste it into the empty spreadsheet.
- Name and save the template. When you want to use this custom template, make a copy so you don’t change the original template file.
This article explains how to create your own template in Google Sheets when one of the Google Sheets pre-supplied templates won’t work. You’ll need a Google account to access Google Sheets. Sign in to Google Sheets with the same account you use to access Gmail or YouTube.
What You Need for a Custom Google Sheets Template
A Google Sheets custom template contains information specific to the spreadsheet you want to make. To make a custom template, start with a spreadsheet file that contains the information and formatting you want to use in the template.
Create the spreadsheet file in any spreadsheet program, such as LibreOffice or Microsoft Excel. You can even make the spreadsheet in Google Sheets, either from scratch or from the Template Gallery. Just open one of those templates and edit it as needed to fit your project.
Make a Folder for Custom Templates
To keep your custom templates organized, create a folder that will contain only template files.
Open Google Drive and go to the root folder (the top folder and not a subfolder).
Select New > Folder.
In the New folder dialog box, enter a descriptive name for the folder, then select Create.
The new folder appears in the list with the other folders in Google Drive.
Add Your Template to the Folder
To add your custom template to the newly created folder:
Open the templates folder you created.
Select New > Google Sheets to make an empty spreadsheet that will be used as the template file.
If you select From a template, the Google Sheets Template Gallery displays. You cannot upload templates or create a blank template from the Template Gallery.
Open the spreadsheet that contains the data you want to use in the template and highlight its contents. To select everything in the spreadsheet, press either the Ctrl+A or Command+A keyboard shortcut.
Select Edit > Copy to copy the highlighted content. Or, press Ctrl+C or Command+C.
Open the empty spreadsheet you made in Step 2 and select Edit > Paste to paste the spreadsheet contents. Or, press Ctrl+V or Command+V.
Select the cell at which you want the paste to take place. For example, if you copied everything in the spreadsheet, select the square to the left of A and above 1 to highlight the entire sheet, then paste the spreadsheet contents.
Enter a descriptive name for the template.
Select the Google Sheets icon to return to Google Sheets.
Use Your Custom Templates
When you want to use this custom template to create a new spreadsheet, make a copy of the template file before you make any changes to the original template file. If you edit the original template, you won’t have that unaltered template available for use in the future.
To make a copy of a template, right-click or tap-and-hold the template file, then choose Make a copy.
Then, rename the copy and move the copy to another folder so that you don’t accidentally edit the template.
After you have previewed a template for Google Sheets or Google Docs and decide you want to use the template, what is the best way to get a copy you can edit?
What To Do: Click on the Use Template button. If you don’t see the Use Template button, then go to File > Make a Copy. These actions place a copy of the template on your Google Drive for you to use and edit.
If you click on View Only > Request Edit Access, you are requesting access to edit the original template, and I don’t grant access to edit the original. Instead, you need to get your own copy of the file, and you do that by going to File > Make a Copy.
NOTE: If you see the original template listed in your Google Drive (the owner will be listed as jonwittwer or jon), then you can safely delete that from your Google Drive. This will not delete the original – it is only a pointer to the original template.
How to Open Excel Templates in Google Sheets
Within Google Drive, go to New > File Upload and select the Excel file from your computer. To find the file you uploaded in Google Drive, click on the Recent tab in the left sidebar. Double-click on the file you uploaded then click on Open With Google Sheets.
Google Sheets recently made it possible to edit Excel files without having to first convert them to Google Sheets. If you still want to convert the Excel file to Google Sheets, you can do that by clicking on File > Save as Google Sheets as shown in the image below.
Not all of my Excel templates are fully compatible with Google Sheets. Although Google Sheets is rapidly getting better, there is still a long way to go before it can do everything Excel can do. For that reason, I usually have to create separate templates specifically for Google Sheets.
If there is an Excel template on Vertex42.com that you would like to work with in Google Sheets, please request it by commenting below.
If you find yourself creating the same spreadsheet outline over and over again in Google Sheets, you can save yourself massive amounts of time if you create a template. We’ll walk you through the process step-by-step.
Google Sheets has a wide selection of templates you can choose from, but it’s possible none of these will meet your specific requirements. If you want to create a custom template you can use whenever you need it, there’s a work-around you can use to get started.
First, fire up your browser and head to Google Sheets. Open a blank spreadsheet, a spreadsheet you created previously, or one of Google’s templates.
Now that you’ve got some “bones” for your spreadsheet template, click the filename at the top of the sheet and rename it to something that includes the word “template.” Press Enter when you’re finished to save your changes.
Next, click the folder icon next to the filename, and then select the “New Folder” icon to create a new folder for your Google Sheets templates.
Type a name for the new folder and click the check mark to create it.
Click “Move Here” to save the template in your new Google Sheets template folder.
This folder can now be the new home for any future templates you create. This is also a great way to keep everything in your Google Drive neatly organized and easy to find for anyone who’s part of your team.
When you need to make a copy of any of your templates, head over to Google Drive, find the folder you just made for your templates, and double-click it.
Because this is a template file you plan to use multiple times, you should first make a copy of it before you start editing or adding info to it. To do so, right-click the template and select “Make a Copy” to duplicate the file.
The copy of your template saves in the current folder with the prefix “Copy of.” From here, you can double-click the file to open it, rename it, or move it to another folder altogether.
If you open the document, or someone sends you a template file, click File > Make a Copy to copy the template to your Drive.
Name the file, choose a location for it in your Drive, and then click “OK” to save it.
That’s all there is to it! If you want to share your template with other team members, you can create a “Make a Copy” link and send it to them in an email or share it via a shareable link.
If you pay for your G Suite account, you can save custom templates in your custom template gallery for all your team members to use. If you use the free version of G Suite, you can use the method outlined above. Free accounts can share custom templates without any additional expenses.
Google Sheet templates make it easy to get started with nearly any spreadsheet task you can imagine.
But with thousands of Google Sheet templates available, it’s overwhelming to find just the one for your needs.
So we curated a list of our favorite Google spreadsheet templates, featuring everything from personal finance to managing recipes, calendars, project management, running a business and much more. We’ll update this list often.
Google Sheets is a database, a calculator, an organizer, an everything-tracker. It can keep tabs on your recipes, power your website, crunch complex data, help run your business, and list your to-dos.
Enjoy the templates below. Please let us know if you have suggestions to add to the list!
Google Sheets Calendars
Personal Finance Google Sheet Templates
Spending Tracker Templates
Budgeting Templates
- Foundation Template for Google Sheets (free version) – This free budget spreadsheet includes Monthly and Yearly views, with charts, visual trends, quick insights, and sheets for tracking your transactions and yearly balances. Learn more
- Monthly Budget by Google – A user-friendly income and expense tracker. Also provides a dashboard that can be customized with income and expenses. Open in Google Sheets →
- 50/30/20 Budget Snapshot – Create a personal finance snapshot with this simple template. Quickly see what you can afford to spend and save. Open in Google Sheets →
Financial Independence Spreadsheets
Top Templates for Freelance & Small Business
Google Sheets Invoice Templates
Google Sheets Schedule Templates
- Daily Schedule from Google – a simple daily schedule set up in half-hour increments. Easily customizable. Open in Google Sheets →
Marketing Templates
Google Sheets is a marketer’s best friend. We recommend the following resources to find excellent marketing spreadsheet tools. Even better, most of them are free.
- 100+ Google Sheets Templates for Marketers by Sheets for Marketers – for audits, competitor research, Google analytics, content planning and much more.
- Supermetrics Google Sheets templates (some free, some require paid account).
- 10 Ready-Made Spreadsheet Templates for marketers from Buffer.
Project timeline Google Sheet templates
Google Sheet project management templates
Contact list and CRM Google templates
Life Tracking Spreadsheet Templates
Recipe and meal tracking templates
Checklists for Google Sheets
- Simple to-do list – free in Google Drive. Open in Google Sheets →
- Vacation packing list – Open in Google Sheets →
Wedding Planning Spreadsheets
Weddings are notoriously expensive, but they don’t have to be, especially if you use this spreadsheet to plan each expense.
Support — WordPress.com
Shortcodes
Google Docs
Google Docs is a popular service for managing documents online. You can easily embed your Google Docs spreadsheets, documents, forms, and presentations into your WordPress.com site by copying and pasting the embed code.
Table of Contents
- Privacy
- Docs
- Google Drive
- Sheets
- Slides
- Forms
- External Documents
Privacy
Before you share a Google Docs file, please make sure that the document is public. If it is private, you’ll be the only one who can see it. To change your privacy setting, click the Share link at the top of the screen to make it public.
Note: If you’re using the block editor, you will need to paste the embed codes from the following instructions using the Custom HTML block.
If you’re using the Classic Editor, the embed codes need to be pasted in the HTML tab of your editor.
To embed a Google Doc document into your site, open the document. Then click Publish to the Web under the File menu.
Click the Publish button.
Then click the Embed tab and copy the embed code.
Paste the embed code into a Custom HTML block and it will show up on your page or post.
Do you want to embed a PDF file without Google Docs? See Embedding a PDF File.
The WordPress.com Add-on for Google Docs lets you write, edit, and collaborate in Google Docs, then save it as a page or blog post. Your images and most formatting will carry over too. No more copy-and-paste headaches!
Google Drive
To embed a document, for example, a PDF from Google Drive, right-click the document, and click Share. Then click Change to anyone with the link. This will make the document public.
Then click Copy link and open that link in a new browser tab. This will show the document in full screen. Click the three vertical dots near the top right and click Embed item.
A pop-up will appear with the code inside. Copy and paste the embed code into a Custom HTML block and it will show up on your page or post.
Sheets
To embed a Sheet from Google Docs, open the spreadsheet you want to embed and click on the Publish to the Web link under the File menu.
Click the Publish button. Then click Embed and copy the code.
Paste the embed code into a Custom HTML block and it will show up on your page or post.
Slides
To embed Slides into your site, open the project and Publish to the Web button under the File menu.
Click the Publish button.
Click the Embed tab. There are a few dropdown menus to control the size and speed of the slides. Then, copy the embed code and paste it into a Custom HTML block and it will show up on your page or post.
Forms
To embed a Form, click Send button at the top-right corner when editing a form.
Then, switch to ‘code’ tab (the one with ) and copy the entire embed code. Then paste the embed code into a Custom HTML block and it will show up on your page or post.
Note: The post/page must be published for the form to appear.
External Documents
To embed a document (such as a Microsoft Word document) without converting it to Google Docs format, you can open your Google Drive on the browser and click New and then click File Upload.
After the upload completes, right-click the document, and click Share. Then click Change to anyone with the link. This will make the document public.
Then click Copy link and open that link in a new browser tab. This will show the document in full screen. Click the three vertical dots near the top right and click Embed item.
A pop-up will appear with the code inside. Copy the code and paste it into a Custom HTML block and it will show up on your page or post.
The embedded file will now show up when you preview or publish your post or page.
Versatile plans and pricing
Best for students
Personal
Best for hobbyists
Premium
Best for freelancers
Business
Best for small businesses
Still confused?
Help us improve:
We’re always looking to improve our documentation. If this page didn’t answer your question or left you wanting more, let us know! We love hearing your feedback. For support, please use the forums or contact support form. Thanks!
Google Sheet templates make it easy to get started with nearly any spreadsheet task you can imagine.
But with thousands of Google Sheet templates available, it’s overwhelming to find just the one for your needs.
So we curated a list of our favorite Google spreadsheet templates, featuring everything from personal finance to managing recipes, calendars, project management, running a business and much more. We’ll update this list often.
Google Sheets is a database, a calculator, an organizer, an everything-tracker. It can keep tabs on your recipes, power your website, crunch complex data, help run your business, and list your to-dos.
Enjoy the templates below. Please let us know if you have suggestions to add to the list!
Google Sheets Calendars
Personal Finance Google Sheet Templates
Spending Tracker Templates
Budgeting Templates
- Foundation Template for Google Sheets (free version) – This free budget spreadsheet includes Monthly and Yearly views, with charts, visual trends, quick insights, and sheets for tracking your transactions and yearly balances. Learn more
- Monthly Budget by Google – A user-friendly income and expense tracker. Also provides a dashboard that can be customized with income and expenses. Open in Google Sheets →
- 50/30/20 Budget Snapshot – Create a personal finance snapshot with this simple template. Quickly see what you can afford to spend and save. Open in Google Sheets →
Financial Independence Spreadsheets
Top Templates for Freelance & Small Business
Google Sheets Invoice Templates
Google Sheets Schedule Templates
- Daily Schedule from Google – a simple daily schedule set up in half-hour increments. Easily customizable. Open in Google Sheets →
Marketing Templates
Google Sheets is a marketer’s best friend. We recommend the following resources to find excellent marketing spreadsheet tools. Even better, most of them are free.
- 100+ Google Sheets Templates for Marketers by Sheets for Marketers – for audits, competitor research, Google analytics, content planning and much more.
- Supermetrics Google Sheets templates (some free, some require paid account).
- 10 Ready-Made Spreadsheet Templates for marketers from Buffer.
Project timeline Google Sheet templates
Google Sheet project management templates
Contact list and CRM Google templates
Life Tracking Spreadsheet Templates
Recipe and meal tracking templates
Checklists for Google Sheets
- Simple to-do list – free in Google Drive. Open in Google Sheets →
- Vacation packing list – Open in Google Sheets →
Wedding Planning Spreadsheets
Weddings are notoriously expensive, but they don’t have to be, especially if you use this spreadsheet to plan each expense.
Forget the pen and paper method
Photo by rawpixel on Unsplash
Checklists are underrated. They are often overlooked, and don’t get the snaps they deserve for being an imperative organization and task management tool. Not only are they super practical for learning or remembering set processes, but did you know that they can also help save lives?
With an aim to reduce error by making up for the restrictions of human memory, checklists are a handy tool that can assist in many everyday situations, whether at work, school or at home. Simple to create and even more simple to follow, using one entails following the required steps that have been developed for a particular recurring process, and then checking off an item as you complete it.
Using a checklist to optimize efficiency
The first thing I do when setting out to write a new article, like many other blog writers out there, is to create a pre-writing checklist. Although writing a blog article is something that I’ve done many-a-time, following a checklist helps to ensure that I don’t leave out any vital bit of info, or skip a step in the process (I can hear my editor say to me, “did you include this keyword, and how about that link?”). Along with making sure I don’t forget anything, following a checklist also provides structure, boosts my organization game, and improves productivity.
It doesn’t require much to construct a checklist. In fact, the bare minimum is just a piece of paper and a pen. While there’s absolutely nothing wrong with the old school method, why limit yourself to one method when there are plenty of other tricks for you to try? For instance, creating an online checklist with Google Sheets.
Creating a Google Sheets checklist
Google Sheets is a cloud-based spreadsheet offered as part of Google’s Google Drive service along with Google Docs and Google Slides. It is free to use and requires no installation — all you need is an internet connection and a web browser.
Like any other spreadsheet, Google Sheets simulates a paper worksheet by holding data in a tabular format as an electronic document. It also includes features that allow you to catalogue your data in pretty nifty ways. One of those features is a checkbox. By inserting a checkbox beside each of your checklist items, it allows you to create a checklist. Here’s how…
Step 1:
First thing’s first, list the steps/items that are required in the process on a new Sheet. Then highlight the rows on the column to the right of your list of steps/items where you’d like to insert the Google Sheet checkbox feature.
Step 2:
Once the chosen cells are highlighted, click onto Data validation, which is found under Data on your toolbox.
Step 3:
Under Data validation, select ‘List of items’ under the Criteria, and then copy/paste the check and cross characters (we copied the characters from the ‘special characters’ found in Google Docs). Remember to separate the characters with a comma!
Step 4:
Once the settings are saved, a checkbox will appear on the column beside your list for every step. When you click on the dropdown arrow, it will give you the option to select a check or cross character.
Voila! There’s your Google Sheets checklist.
Creating a checklist on Google Sheet is ideal for simple lists. While it works visually, and it does allow you to check off each item as you go along, it is not the most intuitive software if you wanted to create a more complex checklist.
Let’s face it, when writing down the steps of a process, there will be times where you have to rearrange the order, either by intent or accident, and when you do so, don’t you want an easier way to do it all rather than having to delete and re-write the list then individually add each checkbox beside it again?
Update: Since this article was originally published, a Google Sheets checkbox feature has been introduced in their Google Sheets service. It can be found under the ‘Insert’ tab.
The alternative solution…
Creating a checklist with Zenkit allows you to track progress visually and mark things off as they’re completed with taking little to no steps to do so.
Simply create a new Collection, give it a checklist title, and start listing the steps/items that aren’t to be forgotten. The recommended view, of course, is the ‘List view’, however, the beauty of using Zenkit is that every view — bar the table view — can be turned into a checklist!
To make any Zenkit collection into a checklist, just enable the ‘Task List’ add-on. (Here’s how.)
Checking things off will be a breeze as the feature enables you to tick a box beside the item to mark it as ‘checked’, and then it will automatically move that item down the list — how’s that for prioritizing?
Google Sheets checklist template alternative:
Another cool thing to note is that you can use the checklist collection you set up in Zenkit as a template. Keep a list you’re happy with and use the Clone Collection function to duplicate it whenever you need a new checklist.
Using a checklist not only allows you to complete your tasks quicker and accomplish more, but they also allow you to be more creative as having the important steps written down will make room for more creative brain power to be utilized.
So, whether you create a Google Sheets checklist, or one on Zenkit, or even use a piece of paper and a pen, it can be a foolproof way to ensure that the task you set out to do gets completed correctly and efficiently.
What do you use a checklist for?
Dinnie and the Zenkit team
A casual look through any mobile app store and you’ll notice a huge number of calendar apps available. Many of which charge a fee simply for using them, or to access fairly basic features. Paying for such a basic service can seem problematic, especially as free calendars are easily available online.
If you want to create your own calendar, you can do this using Google Sheets. Creating your own calendar gives you far more control over exactly how your calendar looks and functions. You can use it online, or print it off to use as a pen-and-paper desktop calendar.
If you want to make a calendar in Google Sheets, here’s what you’ll need to do.
Using a Google Sheets Calendar Template
The simplest way to create a quick and easy calendar in Google Sheets is to use one of the ready-made templates available to users.
To do this, head to the Google Drive website—you’ll need to sign in if you haven’t already. Once you’re signed in, click the New button on the left to create a new document.
Select Google Sheets and hover over the > symbol.
From the pop-up menu, select From a template.
Choose one of the calendar templates—a new sheet will be created containing your chosen calendar design.
Creating a Calendar from Scratch
Any document you create from a template can be edited to suit your needs, but you might be looking to create a calendar with a design that differs from the available templates. If this is the case, you’ll need to create your calendar from scratch.
Adding Days to a Custom Google Sheets Calendar
To start, open a blank Google Sheets document. You’ll need to insert the days of the week to begin—choose the starting cell for your calendar, and type a day you want to start with.
Select the cell you’ve just typed in, then press and hold down the small square in the bottom right-hand corner of the cell.
Drag the cursor horizontally or vertically to fill additional cells with days of the week. In this example, we’ll have our days of the week arranged vertically.
You can continue dragging as far as you want if you want to create an entire month or even year of dates.
Adding Dates to a Custom Google Sheets Calendar
Next, we need to add the date. Checking that you have the cell for the correct day of the week, type the date in the format [month]/[date].
If you use a different date format, go to File > Spreadsheet settings and change the locale to your own. This will change various default formatting options to match your locale, including date formats and local currency.
Next, click on the initial date cell to select it, then press Format > Number > More Formats > More date and time formats to change how the date is displayed.
Select the cell you’ve just edited, press and hold the small square in the bottom-right corner of the cell, then drag downwards to fill your calendar with dates.
You now have a basic structure to your calendar in place, with named days and dates provided. You can expand it further by adding columns containing other important data.
If you want to make your calendar look a little cleaner, you can hide gridlines in Google Sheets, too.
Creating a Google Sheets Calendar
As we’ve shown above, creating a Google Sheets calendar is an easy process. It offers a good alternative to using paid-for apps or online services, as you’re able to use Google Sheets in offline mode.
Creating a calendar in Google Sheets also allows you to make use of the other Google Sheets features. For example, you can set up your spreadsheet to send an email based on a cell value. If your calendar is shared, you could set alerts to tell you when a task has been completed by updating cells from pending to complete.
You can also control exactly which sections of your calendar can be edited by other users by learning how to lock cells in Google Sheets, letting you share it with other users.
When it comes to mass data entry works, it is essential to use data entry forms to improve accuracy and speed. This tutorial will show you how you can create a data entry form in Google Sheet. With Google Sheets, it has many advantages over data entry forms like MS Access installed on your local computer.
The default form option comes with the Google sheet (Google Form) is not suitable for mass data entry works. Instead of that, you can use Google Apps-Scripts to build a data entry form in Google Sheet or to build web apps.
In this post, I will show you how to create a data entry form in Google Sheet itself to use for mass data entry works.
In the previous post, I explained, “How to Create a Dependent Drop-Down List in Google Sheet“. Here I am going to use those two fields as part of the data entry form.
Creating the form interface
You can simply create the form interface by taking the cells as input fields. The following form includes six input fields namely “Region”, “Country”, “Population” “GDP”, “Area”, and “Literacy” in D4, D6, D10, D12, G10, and G12 cells respectively.
Figure 01: Data entry form
You can protect the entire sheet except the input fields to prevent users from editing the other cells.
The “Save” button
Here I am using an image as the save button. (Later I will assign apps script function to this image to copy the data into another sheet once you clicked it).
To create this image go to, Insert > Drawing and then draw a rounded rectangle using the Rounded Rectangle shape tool. Then add your text and add colors using color tools.
Apps script to copy data from the form to another sheet
Once you click the save button, the data in the input field should copy to another sheet. This task can be achieved through Google Apps Scripts.
To access Script Editor, go to Tools > Script editor. Give a name to your project. Then copy the following script to the script editor and save.
Rename your sheet which includes the form as “Form” and another sheet as “Data”. We are going to copy the data in the Form to this “Data” sheet.
Assign Apps Script to save button
Now you can assign the function submitData() to the save button (actually the image). To do that, click on the image and then click the menu icon in the top right corner of the image. Then select the “Assign script” option. In the text box type your function name, submitData and click OK.
Now you can fill the form and copy the data to the “Data” sheet by clicking the save button.
Make a copy of the above Google Sheet
Go to Tools > Script editor to view the code
What next?
The above code does not clear the input fields after submitting the data. You can use clear() function to clear the required fields after submitting the data to the “Data” sheet.
The data validation part is another essential part in data entry forms. You can check the values of the field before they copying to the “Data” sheet using conditional statements. Then you can provide meaningful error messages to the user using Browser.msgBox(“Error message!”) function.
Even though this method is better for mass data entry works than Google Forms, there are a number of other issues. You have to grant edit permission to the user to the “Data” in order to copy the form data to it. Therefore some errors can happen during the data entry. The form is also not much user-friendly.
In order to overcome those issues, you can build your own web app using Google Apps Script HTML Service.
If you wish to create a more advanced data entry form for Google Sheets you may read our other tutorial on web apps.
Wrapping Up
In this tutorial, I showed you the easiest methods that you can use to create a data entry form in Google Sheet. However, as mentioned above, there are some disadvantages to using this method. Some of these can be overcome by creating Sidebar & Modal Dialog forms in Google Sheets.
When it comes to mass data entry works, it is essential to use data entry forms to improve accuracy and speed. This tutorial will show you how you can create a data entry form in Google Sheet. With Google Sheets, it has many advantages over data entry forms like MS Access installed on your local computer.
The default form option comes with the Google sheet (Google Form) is not suitable for mass data entry works. Instead of that, you can use Google Apps-Scripts to build a data entry form in Google Sheet or to build web apps.
In this post, I will show you how to create a data entry form in Google Sheet itself to use for mass data entry works.
In the previous post, I explained, “How to Create a Dependent Drop-Down List in Google Sheet“. Here I am going to use those two fields as part of the data entry form.
Creating the form interface
You can simply create the form interface by taking the cells as input fields. The following form includes six input fields namely “Region”, “Country”, “Population” “GDP”, “Area”, and “Literacy” in D4, D6, D10, D12, G10, and G12 cells respectively.
Figure 01: Data entry form
You can protect the entire sheet except the input fields to prevent users from editing the other cells.
The “Save” button
Here I am using an image as the save button. (Later I will assign apps script function to this image to copy the data into another sheet once you clicked it).
To create this image go to, Insert > Drawing and then draw a rounded rectangle using the Rounded Rectangle shape tool. Then add your text and add colors using color tools.
Apps script to copy data from the form to another sheet
Once you click the save button, the data in the input field should copy to another sheet. This task can be achieved through Google Apps Scripts.
To access Script Editor, go to Tools > Script editor. Give a name to your project. Then copy the following script to the script editor and save.
Rename your sheet which includes the form as “Form” and another sheet as “Data”. We are going to copy the data in the Form to this “Data” sheet.
Assign Apps Script to save button
Now you can assign the function submitData() to the save button (actually the image). To do that, click on the image and then click the menu icon in the top right corner of the image. Then select the “Assign script” option. In the text box type your function name, submitData and click OK.
Now you can fill the form and copy the data to the “Data” sheet by clicking the save button.
Make a copy of the above Google Sheet
Go to Tools > Script editor to view the code
What next?
The above code does not clear the input fields after submitting the data. You can use clear() function to clear the required fields after submitting the data to the “Data” sheet.
The data validation part is another essential part in data entry forms. You can check the values of the field before they copying to the “Data” sheet using conditional statements. Then you can provide meaningful error messages to the user using Browser.msgBox(“Error message!”) function.
Even though this method is better for mass data entry works than Google Forms, there are a number of other issues. You have to grant edit permission to the user to the “Data” in order to copy the form data to it. Therefore some errors can happen during the data entry. The form is also not much user-friendly.
In order to overcome those issues, you can build your own web app using Google Apps Script HTML Service.
If you wish to create a more advanced data entry form for Google Sheets you may read our other tutorial on web apps.
Wrapping Up
In this tutorial, I showed you the easiest methods that you can use to create a data entry form in Google Sheet. However, as mentioned above, there are some disadvantages to using this method. Some of these can be overcome by creating Sidebar & Modal Dialog forms in Google Sheets.
