4

I am having problems with Sheets and Forms connection.

I have pre-entered formulas on Google Sheets, like vlookups and TO_DATE value, but as soon as I submit entries from Google Forms, the formulas that I typed in before were erased and the cells are now blanks.

Form--

  • Employee ID
  • Time Log
  • Remark

SheetA--

  • Timestamp
  • Employee ID
  • Time Log
  • Remark
  • Date - To_DATE value from Timestamp
  • Full Name - vlookup from SheetB

Links to form and sheets:

Form - https://forms.gle/qPKhwZdYc7UHX9W67

Sheets - https://docs.google.com/spreadsheets/d/1bzPHrIb24JbwNHsmkbFQGvmla8bwkXNCCyG-815AH6I/edit?usp=sharing

2
  • If you will share a link to the sheet, being sure to set the link's Share permission to "Anyone with the link..." and "Editor," I will take a look. I can tell you that your formulas are not being deleted; rather when new entries are added by form, new rows are also being added by the form. Formulas must be set up a specialized way to avoid that happening. – Erik Tyler Feb 17 at 3:42
  • Hi @ErikTyler, I edited my question and added the needed links. Thanks! – Joshua Dela Cruz Feb 17 at 5:11
4

To start, you should never add anything, manually or via formula, to form intake sheets. It nearly always leads to problems. Leave the raw form data alone. That said, my first suggestion is that you delete not only your additional headers and formulas in sheet 'A' (the form intake sheet as currently named), but that you delete all columns other than those being used by the form intake fields. To do this, click on the 'E' at the top of Column E, scroll over to Column L, hold down the Shift key, and click the 'L' at the top of Column L. Right-click and choose "Delete columns E-L."

Next, as you will see, I've set up a new sheet called "Erik Help." You can rename this to whatever makes sense to you. Currently, this sheet is protected such that only you and I can access it. You can change that as you wish.

In this sheet, you'll notice that formulas only exist in Row 1. These formulas are array formulas, each of which generates the header and all results for the respective column. This is the first key to dealing with processing incoming form data without problems.

Second, you'll notice that each formula references the entire column from sheet 'A' (e.g., A:A, C:D, etc.). That is, you will not see any references like A2:A. This is the second key to dealing with processing incoming form data. If you use references other than whole-column references, as soon as new rows are added to the form sheet, references like A2:A self-adjust to become A3:A and so on.

Finally, you'll see that there are basically two kinds of formulas used in my sheet.

Type 1:

=ArrayFormula(A!A:A)

This type simply brings over the entire column as-is from the form sheet. If you want to bring over contiguous columns, you can do that (as I've done in Column F of my sheet where the formula is =ArrayFormula(A!C:D)).

Type 2:

=ArrayFormula(IF(ROW(A!A:A)=1,"Date Only",IF(A!A:A="",,INT(A!A:A))))

This is a processing formula. It changes something about the raw form data. Notice a key setup. In order to form a header and still reference the entire raw data column, we use this:

(IF(ROW(A!A:A)=1,"Date Only"

This says, "Only if the raw-data row is Row 1, enter this string in cell 1 (which becomes the new header).

If it's any other row than Row 1, something different will happen to every row in the range from Row 2 down. In the case of the example given, this is what will happen for each of those other rows:

IF(A!A:A="",,INT(A!A:A))

For any raw-data row in the referenced column that is blank, this part of the formula will leave the corresponding cell in that row blank as well (that is the two commas side by side, which essentially means "do nothing"). For any raw-data row in the referenced column that is not blank, the INT value of the original data will be output to the corresponding cell. (In the case of date/time, this will reduce them to date only.)

Every formula in my sheet follows from these two examples.

Note that all references that create the header or check for blanks should be to the same sheet as the final portion of the formula, because all ranges have to be the same length. In this second formula above, the final part of the formula is INT(A!A:A), so we reference A!A:A everywhere else in the formula. But in another formula (cf., the formulas in Columns D and E), the final comparison is to something in the same sheet, so the header and blank-check portions are also to the current sheet, not the raw-data sheet. In sheets that have exactly the same number of rows at all times, this wouldn't matter. But if the raw-data and processing sheet vary by even as much as a single row, formulas that referenced the two different sheets within themselves would begin to error due to the mismatch between column lengths.

2
  • Oh, wow! This is very useful! Very useful indeed! – Joshua Dela Cruz Feb 17 at 8:56
  • @JoshuaDelaCruz, I made a slight tweak to my second formula variation, both in my post above and in your sheet. See my additional paragraph in the post for explanation. – Erik Tyler Feb 17 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.