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.