How to Auto-Update Auction Bids Through Google Forms

​I have run auctions through Google Forms now for a couple reasons: Ebay takes 15%+ of sales and Dealer's Den requires people to pay a fee to authenticate their account to bid.

The first time I ran an auction I manually updated the bid amount in the form which required me to constantly keep an eye on emails. The second time, I figured out a way to link the form to a spreadsheet that logs the bids and auto-update upon submission!

My method:

• displays an opening bid amount when there are no bids
• requires at least the opening bid amount to be entered
• automatically updates the bid amount on the Google Form upon each submission
• displays the current bid and a snippet of the person's email
• displays the first person to submit the amount in the event multiple people bid the same amount

This tutorial might be difficult to follow if you don't have much Excel or Google forms experience but I hope that you can follow it regardless (and I'm sorry if this technique is rough and ready for any expert coders out there).
​

First of all, do all of this on a PC and not on your phone.
​
Create a new Google Form. Name it whatever you'd like. Mine is going to remain Untitled in this example. Under Settings, require emails to be collected.

Click the triple dot drop-down menu on the top right of the form. Click Add-ons.
​

Search for "Form Ranger" and install the highlighted one above. It will ask for permissions which you must grant. This Form Ranger will allow you to link a question on your Form to a range on a spreadsheet.

(If you have difficulty installing it, it may be due to having multiple gmail accounts. Try opening an incognito window, logging onto your desired google account there, and installing it in that window).
​

​Add a question to your form. This will not function as a question but as a place to display your current bid. I title it "Current bid"; you can specify the currency if you want. I place it under the required email address question so that people can quickly check the form without having to scroll too much. (So, above where they will place the bid).

Make it a multiple choice question with only one answer. Don't worry about it saying 'option 1' right now.

Add any other questions you want in your form from your bidders like contact information, location, etc. We will add the question where they bid below.

Create your question where people type in their bid. I place this at the end of the form so I can ensure people fill out the rest of the questions. (Side note: I only make the email and bid required; the rest of the questions are optional, and in the description of the question, I clarify they only have to fill these out once per auction. If you're looking for the description of a question, it's in the triple dot drop down).

Set it to short answer. In your question make sure you clarify what currency the bid is in for your customers, and what it must be higher than. We will make it only accept numbers (that means no $, commas, etc). When the opening bid is made, you can go back into the form and delete the "must be greater than [minimum]" if you want.

On the triple dot drop down on the bottom right of the question select response validation.

On the first drop down select "number", and then select "Greater than or equal to". Then type in your minimum/opening bid (800 for this example). It will ask you to type an error message. Type something that says to enter higher than the opening bid and to type only a number without symbols.

​This will limit the bids to an amount greater than the opening bid and also requires only numbers for our formulas that read the bids later on.

Make this question required.
​

You can see above that typing in anything less than the opening bid or any character results in an error.

When you're done creating all of your questions in your desired order and format move on.
​

Click on the Responses tab and click "Create Spreadsheet" (the green and white icon). This will create a spreadsheet that the responses from the form will get sent to.

If you would like to follow along with an example spreadsheet, you can view my spreadsheet here:
​Test Spreadsheet Example

The new spreadsheet will automatically label a column for a timestamp + each question you entered into your form. Even if it's not a required question on your form, a column will appear. Even our placeholder for where the current bid will appear is on the spreadsheet.

First, let's talk about how the data comes into the spreadsheet. Each time someone submits a form it will enter a new row of data. It will timestamp the entry automatically, display their email, and their response for each question. New entries will be added below the last one.
​

At the bottom of the spreadsheet click the plus button to add a new sheet.
​

​On the new sheet, which should be called Sheet2 (name it something if you want, doesn't matter), title the first 3 columns in row 1 "High Bid", "High Bidder", and "Current Bid". Names don't really matter but this is what I'll be referring to them as.

The High Bid column is going to find the highest dollar amount bid, if there is one.

The High Bidder column is going to find the email of the person who bid the highest amount above the opening bid.

The Current Bid column is going to combine the two together for a value to display in your form, but only display a snippet of the email address for security purposes. This will be the only cell actually sent back to your form.

In cell A2 type or copy and paste the following formula, with the purple letters being specific to your spreadsheet:

=MAX('Form Responses 1'!D:D)

The purple numbers are where your bids fall into your 1st spreadsheet. For me, it's column D.

If you get a REF! error by pasting in my formula, then your 1st sheet is probably named something else. The Form Responses 1 must be equal to the exact name of your first sheet.

To edit the formula, click into the area after the "fx" towards the top where you see the formula appear. Arrow over to the D:D and delete the D:D and type in your own column letter yourself.

Alternatively, you can click on cell A2, start typing =MAX(, then clicking on your first sheet on the bottom left of Google Sheets. Then select the entire column by clicking the letter of your column, not the individual cell below it.

This formula pulls the maximum bid. If there are no bids, a 0 will show (as seen above; I deleted the data shown in the example above).
​

In cell B2 type or copy and paste the following formula, with the colored letters being specific to your spreadsheet:

=if(A2=0,"Opening Bid",INDEX('Form Responses 1'!$B:$B,MATCH(A2,'Form Responses 1'!$D:$D,0)))

If you're in a European country (or possibly others!) they use semicolons ; instead of commas , in formulas! So, you may need to use:
=if(A2=0;"Opening Bid";INDEX('Form Responses 1'!$B:$B;MATCH(A2;'Form Responses 1'!$D:$D;0)))
You will know this if you get a #ERROR "formula parse error" as a result when everything is correct.

The purple letters are where my email addresses are located. It should usually be in column B if you require it with the Google Forms settings.

The blue letters are where your current bids are on the first sheet. Again, column D for me.

This formula uses index/match which is used to match the current high bid to the email address that bid that amount. (For those familiar with Excel, we can't use a vlookup since the email address is located before the bid amount.)

The formula is complex, but you can read it along the lines of: "If I have no bids, display 'Opening Bid'. Otherwise, grab the email address of the highest bidder."

Now let's see what happens if you have a bid equal to the opening bid:
​

High bid is now displaying 800. The index/match function finds that "[email protected]" is the person who bid that amount.

What happens if 2 people bid $800?
​

Another $800 bid comes in much later than the 2nd one.
​

"test" is still the highest bidder, because the index/match grabs the first instance of the $800 occuring. This is because the first sheet is sorted in chronological order (this should be done automatically).
​
Now, we want to combine our bid amount and bidder into a single cell to display on our Form, but we don't want to display the person's full email address.

​I'll add another test bid of $900 by "test4".

In cell C2 type or copy and paste the following formula:

=IF(B2="Opening Bid","$800 Opening Bid","$"&A2&" by "&LEFT(B2,5)&"**********")

Your opening bid amount should be in green.

How many characters you want to show of the email address is in red.

How many asterisks you want to display in purple (is not dependent on how long the person's email actually is).

This formula looks complex but it's mostly formatting. You can read it as, "If no bids have been made (i.e. cell B2 is equal to "Opening Bid"), display the opening bid amount. Otherwise, display the current bid with a dollar sign, "by" and then a fraction of the email address."

The formula is:

• displaying "Opening Bid" instead of a name if there are no bids
• putting a dollar sign in front of the High Bid cell (A2)
• displaying the first 5 characters of the person's email with asterisks afterward
  • ​You can choose to display as many characters as you want here
    • ​Don't make it too short in case you happen to have similar emails come through (very unlikely though)
    • Don't make it too long or you may display the whole email​
    • ​​​​Personally I've been doing 4 characters but I displayed 5 for this example
• You can display as many asterisks as you want (I did not make it depend on how long the email address is)​

​
Let's go back to the form.

On the top right of Google Forms click on the puzzle piece and click on the Form Ranger add-on we installed earlier.

(My form has 4 responses from my tests).
​

Click start on the pop up.
​

​On the new pop up select your 'Current Bid' question where you want the bid to be displayed. Check populate from range.

At the bottom, make sure you turn 'On form submit' on. I also turn on every hour to be safe. This is what auto-updates your form.

Click the plus sign next to the 'Select' drop down under the Current Bid question on this new pop up.

On the new pop up click on the spreadsheet that you created in this tutorial. Hit Select.
​

On the Select Range tab change the Sheet name to Sheet2 (or whatever you named your 2nd sheet if you changed it).

Under column header change it to Current bid (or whatever you named the joined version of bid+email).

It should show you previews on the right.

​Hit next.
​

Under Range name type in 'Current bid' or something along those lines again. This is just how the Form Ranger refers to it. Hit Save and populate question at the bottom.
​

Now the multiple choice answer will display the bid instead!!

It does not matter if the person clicks the button next to the bid. It's not a required "question".

If for whatever reason you need to manually refresh what displays you can do this by reopening the Form Ranger add-on. If you ever make a mistake filling out the Form Ranger, you can hit the trash can next to the "select" drop down in the Form Ranger to start over)

Let's go through this by deleting all of our test submissions. If you haven't already, I highly recommend testing all of this: see what happens with no bids, bids less than the opening bid, higher, duplicate bids, etc.

To delete our test bids:
​

Click on the 'Responses' tab, click on the triple dot drop down, and hit 'Delete all responses'. It will confirm if you want to do this, and mention that your spreadsheet will not be cleared, which is important! Confirm and open your spreadsheet with your data.
​

Highlight the entire range, starting at row 2. Hit the 'delete' button on your keyboard. Do not 'delete rows' by right clicking.

Now let's look at our form.
​​

It still displays our test bid, even though we deleted the data. This is because Form Ranger needs to be manually refreshed (no form was submitted, and it was not 'every hour', so Form Ranger would not update this value itself).

Just click on the puzzle piece in the top right again and launch Form Ranger again. It should update automatically once it loads.
​

Then you are good to go!

For fun, we can submit another test bid to see it working on the form (hit the 'preview' button to see it yourself).

Yay! (If it doesn't update when you click 'Submit another response' try refreshing the page; the spreadsheet and Form Ranger can take a moment to update).

But let's look at our spreadsheet:
​

You can see that this entry appears on row 6 and not row 2. This is because these spreadsheets have "memory" and we did not "delete rows" when we deleted the test bids, only cleared the data. To avoid potentially messing with the formulas I preferred just to clear out this data and let the data just do its own thing and appear further down the spreadsheet. The formulas still work just fine, as shown earlier.

If you would like to see this example form and spreadsheet, they are linked below. You are welcome to submit fake bids to the form to see it update. BUT, your email will be sent to the corresponding spreadsheet below which is visible to the public, so beware of this. You can enter in a fake email address as an alternative. Again, you may need to refresh the form if you click 'submit another response' to see it update. You can view the spreadsheet to see the data and the formulas but you cannot edit them.

Test Auction Google Form Example

Test Spreadsheet Example

In an ideal world, I could automate outbid emails to be sent, but that's beyond my coding ability :) I started to try writing a script to do this but that's a bit out of my wheelhouse and I didn't want to potentially email bomb my customers. Instead, I have to do that manually.

I hope this helps! I had a lot of fun figuring this out and it saves a lot of time and energy.

Happy auctioning!