Categories: Ask a "how-to" question : Sheets :

Google spreadsheet form INSERTS a row of data instead of ADDING to the first blank line

Showing 1-4 of 4 messages
Google spreadsheet form INSERTS a row of data instead of ADDING to the first blank line mattlightbourn 3/24/11 12:34 PM
Hi, wondered if someone might be able to help with an issue with a form writing into a google spreadsheet and how it adds the row of data submitted from the form.

A submitted form adds data into a sheet of a google spreadsheet.  On another sheet, I have referenced the form response sheet on row 2 with calculations and then filled the formulas down to row 2000.  (This sheet is referenced using a data query to a data list on my Google Site).  When a new submit happens, it 'inserts' the new form data into the sheet instead of simply, overwriting the next available blank line.  This means that, my formula in Row 5 which was setup to look at row 5 in the form responses sheet, now instead looks at row 6.

The fix would be to get the form to look for next available blank line and enter the data in rather than, what can only be assumed as, inserting a new row and then inputting the data into the new line.

I have seen two other posts similar to this but without the fix I need.  It should be simple and is possibly, a problem with the process in the form submition programming rather than anything else.  The only way I can stop it doing it is to enter in every row manually using a D$5 instead of D5 so that the row does now change its reference.
Re: Google spreadsheet form INSERTS a row of data instead of ADDING to the first blank line brettathds 3/31/11 11:49 AM
I have just encountered the same problem with formulas that I have created in a second sheet.
Re: Google spreadsheet form INSERTS a row of data instead of ADDING to the first blank line mattlightbourn 3/31/11 12:41 PM
Yeah, at the moment I've given up on it because no one seems to know how to set the forms to ADD instead of INSERT.  Very difficult to use calculations on other sheets when it inserts because it means it omits whatever line number has been inserted on the form linked sheet.  :)

Anyone have an answer please?  I think there must be a calculation which recalls an entire column at a time instead of rows and then you can calculate from that additional (yet another) sheet of raw data which doesn't care if its had an insert on it.

Thanks

Matt
Re: Google spreadsheet form INSERTS a row of data instead of ADDING to the first blank line brettathds 3/31/11 1:58 PM
I have figured out a solution using =ARRAYFORMULA()...

In the simplest example, I am pulling all of the values from Sheet1-ColumnA into Sheet2-ColumnA by inserting the following formula into Sheet2-CellA1:

=ARRAYFORMULA(Sheet1!A1:A)

As new items are added through the form, the A column of Sheet1 is properly replicated into Sheet2's A column.


I am also using a similar formula to convert Sheet1 column A values into a decimal time representation in Sheet2 Column E.  In Sheet2-CellE2 I have inserted the formula:

=ARRAYFORMULA(IFERROR(HOUR(Sheet1!A2:A)+ROUNDDOWN(MINUTE(Sheet1!A2:A)/60, 1)))

Using ARRAYFORMULA, there is no need to "drag" the contents of the cells down.  The "A1:A" declaration causes the formula to automatically continue for each of the subsequent cells.