Dynamically creating column names based on data in another tab

Question

I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.

The goal is to have a Google sheet to look like this: (Each tab in the Google sheet is the same name of these forms)

Week Commencing | Number of Submissions
  1st July 2019 | 37
  8th July 2019 | 7

The data I have looks like this: (in a tab named "data")

form name                         | date 
Custom Printed Premium Gift Boxes | 7/4/2019 12:01:28

Link to google sheet: https://docs.google.com/spreadsheets/d/1ffOVvGT7CGV3gXR1HP5kp6Pg1JgD40qRjlt6lwwFbo0/edit?usp=sharing

My questions:

1. How do I count the submissions from the data tab and put the total, by the week, into the correct tab?
2. How do I get the week number, convert it to a correct row title of "1st July 2019" and then have the number of submissions for that very week go into the correct cell?
3. Is there a better way to achieve what I'm doing? Am I square peg/round hole-ing?

Answer 1

• to derive week number:

  =ARRAYFORMULA(IF(LEN(B2:B), WEEKNUM(B2:B, 2), ))

  

---

• to convert it into the 1st day of each week number:

  =ARRAYFORMULA(IF(LEN(B2:B), VLOOKUP(WEEKNUM(B2:B, 2), 
   {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                             DATEVALUE(MAX(B2:B)))), 2),
       TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                             DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), ))

  

---

• to create full summary table:

  =ARRAYFORMULA(QUERY(IF(LEN(B2:B), {VLOOKUP(WEEKNUM(B2:B, 2), 
   {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                             DATEVALUE(MAX(B2:B)))), 2),
       TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                             DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), A2:A}, ),
   "select Col2,Col1,count(Col1) 
    where Col1 is not null 
    group by Col1,Col2 
    label count(Col1)''"))

  

---

• to put it into correct tab (with script - manual recalculation):

  function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  }

  =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), 
  {VLOOKUP(WEEKNUM(data!B2:B, 2), 
   {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                             DATEVALUE(MAX(data!B2:B)))), 2),
       TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                             DATEVALUE(MAX(data!B2:B)))), 
   "d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
   "select Col2,Col1,count(Col1) 
    where Col1 is not NULL 
    group by Col1,Col2 
    label count(Col1)''", 0), "select Col2,Col3 where Col1='"&SHEETNAME()&"'", 0))

  

---

• to put it into correct tab (without script - manual input)

  =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), {VLOOKUP(WEEKNUM(data!B2:B, 2), 
  {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                         DATEVALUE(MAX(data!B2:B)))), 2),
   TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                         DATEVALUE(MAX(data!B2:B)))), 
  "d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
  "select Col2,Col1,count(Col1) 
  where Col1 is not NULL 
  group by Col1,Col2 
  label count(Col1)''", 0), "select Col2,Col3 where Col1='Contact Form'", 0))