Can I add two calculated fields to a Google Spreadsheet based on a Google Form?

Question

I am new to Google Scripts and am hoping for some guidance on improving a manual process I complete in Google Sheets.

To analyse the data I receive on a Google Form I run, I manually add-in (or copy down) two calculated identifier fields, which I use for a series of Index-Match formulas.

As new rows are inserted automatically when a new form is submitted, I have to keep dragging these formulas down the page for all new responses, just to update my tables.

The first identifier field is called "Week Number". This field returns a number representing the calendar week of a date selected in the form by the respondent. - i.e.: in cell F100, the formula is =WEEKNUM(C100)-6) : The first week of the project was the 6th week of the year.

The second identifier field is called "WeekName", which is a concatenation of two fields, the "Week Number" and the Name of the respondent, as selected from the form. - i.e.: in cell G100, the formula is =B100&F100 : whereby the Name data comes from the second column (first form field after Timestamp).

I have looked at basic script iterations of getValue and setValue, but can't figure out how to apply that logic to the logic I use in my formulas.

Any tips?

Answer 1

Short answer

There is no need of scripts. Instead use the built-in arrays handling features of Google Sheets.

Explanation

Google Sheets can handle arrays and automatically expand them. There are some functions like SUMPRODUCT, MMULT, FILTER and QUERY that return arrays but other functions require to be used together with ARRAYFORMULA.

Not all the functions could work with ARRAYFORMULA or will return an array of values. Below are the array formulas that should work for the cases exposed in the question.

Examples

Add the following formulas to row 2.

=ArrayFormula(WEEKNUM(FILTER(C2:C,ISNUMBER(C2:C)))-6)
=ArrayFormula(FILTER(B2:B&F2:F,LEN(B2:B))