• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Forms
  • Sheets
  • General
  • Drive
  • Gmail
  • Sites
  • Apps Script
How to GApps

How to GApps

Your life changer for G Apps

  • About
  • Contact
  • Terms and Conditions
  • Privacy Policy
  • COOKIE POLICY

Add Auto Number to Google Form Response Sheet

February 27, 2020 by SavoE

To easily and uniquely identify each Google Form response is to have a key field. That field should be in the form of a number that will be autogenerated to avoid duplicates. This option is not available in Google Forms, so you have to adapt the Form response sheet to do it for you.

The auto-number formula

The formula that we are going to use to add auto number to Google Form response sheet contains a COUNTA, INDEX, ROW and ARRAYFORMULA functions. For the ones that are only here for the formula, here it is:

=ARRAYFORMULA( ROW (B1:INDEX (B:B, COUNTA (B:B)-1)))

Where to put this formula?

Numbering always goes before the data, so what I suggest is to add a column at the beginning, column A, and push the Form responses in the next column. So, instead of the response sheet to start with “Timestamp” in column A, it will start with the auto number.

To insert a column before column A, just right click on the column heading (in this case “A”) and then from the drop-down menu select “Insert 1 left”. Here is a screenshot:

insert one column Google Sheets

Now, add a name for that column in cell A1 and in the cell A2, next to the responses, add the formula.

Note

Sometimes the new column will keep the date format. If that happens the numbers from the formula will be converted to dates. To fix it just select the whole column and select “Automatic” formating from the toolbar.

Why column B?

It doesn’t have to be column B in the formula, it can be any column that holds responses submitted from the Form. But because I’m using column A to add auto number to google form responses, I will use column B in the formula because it will always have values in it.

The functions

The most important function here is the ROW function. The Row function returns the number of the row for the specified cell, here is the usage: ROW(A9).

Syntax: ROW([cell_reference])

  • cell_reference – [ OPTIONAL – The cell in which the formula is entered by default ] – The cell whose row number will be returned.
  • if cell_reference is a range more than one cell wide and the formula is not used as an array formula, only the numeric value of the first row in cell_reference is returned.

Source: Google Docs Help – ROW function

For the ROW function, we are specifying a range that will return a set of row numbers only if we use the ARRAYFORMULA function. Learn more about ARRAYFORMULA function on Google Docs Help.

To get the range for the ROW function we are going to use the INDEX function that will return the last value of column B using the COUNTA to count the values. The minus one is because we must count from the first cell in the column (B), but we don’t have data in the first row.

add auto number in google form response sheet

This formula returns the number of the entry in the Google Form starting from 1. You can use a more complex formula to change the number if you don’t like to start with 1. Here is an example:

=ARRAYFORMULA( 10000 + ROW (B1:INDEX (B:B, COUNTA (B:B)-1)))

Now your number is going to start at 100001.

I have one article where I use this formula, you can check it here: Generate Invoices using Google Form and Sheets

If you have any more questions just add a comment and I’ll get back to you as soon as possible.


Edit

If you want your auto number to include also letters you can use this formula:

=ARRAYFORMULA ("ABC" & text (ROW (B1:INDEX (B:B, COUNTA (B:B)-1)), "000"))

You are going to get this output:

ABC001
ABC002
ABC003
ABC004

Add auto number using a script

One of the negative sides of using a formula for autonumbering is that it will reset each time there is a change in data. So, if you delete a form response, all the following responses will get new numbers. To avoid this I have created a script for auto-numbering: Google Sheets Script for Auto Numbering Google Form Responses

Filed Under: Forms, Sheets

Follow me on Twitter

Follow @HowToGApps

I can’ manage to answer to all comments, so if you have problems with the implementation or you need help with something you can find me here:

Buy Me A Coffee

Inspire me to keep the website up and running.

67 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

Primary Sidebar

Follow me on Twitter

Follow @HowToGApps

More to See

publish to the web Google Sheets

Embed Google Form, Sheet, Chart and Doc in WordPress

Review and Accept the Permission Guide

Google Script Authorization: Review and Accept the Permission Guide

Google Form Script to Auto Fill and Email a Doc Template

Google Form Script to AutoFill and Email a Doc Template

add timer to google form quiz

Add Timer in Google Form Quiz

Create an Issue Tracking System with Google Form and Spreadsheet

Recent Comments

  • SavoE on Automated e-card sender – Google Spreadsheet Script to Send Birthday Cards via Email
  • Brandon on Automated e-card sender – Google Spreadsheet Script to Send Birthday Cards via Email
  • SavoE on Print Merge or Mail Merge for Google docs
  • Claudio on Print Merge or Mail Merge for Google docs
  • SavoE on Print Merge or Mail Merge for Google docs

Buy Me A Coffee
Inspire me to keep the website up and running.




Footer




Recent

  • Close the Google Form and Force the User to Submit the Responses
  • Google Spreadsheet Prank
  • Print Merge or Mail Merge for Google docs
  • Google Sheets Free Certificate Templates + Print Merge Script (email/PDF)
  • Google Form to Slides + Save to Drive and/or send it in email as a PDF

Search

Copyright © 2023 · How to Gapps

wpDiscuz