Average of updating entries with chosen columns

Question

Given a sheet like this:

    + ------ + ------- + ---------- + ---------- + ---------- + ---------- +
    |    A   |    B    |      C     |      D     |      E     |      F     |
    + -------+ ------- + ---------- + ---------- + ---------- + ---------- +
    |  AVG   |  ITEMS  | Week 3 May | Week 2 May | Week 1 May | Week 5 Apr |
    |=QUERY()|  Item 1 |    1263    |    1255    |    1142    |     956    |
    |        |  Item 2 |    1371    |    1263    |    1023    |    1120    |
    |        |  Item 3 |    1382    |    1257    |    1352    |    1853    |
    |        |  Item 4 |    1429    |    1281    |    1120    |    1869    |

1. I need to move the column B to the first column (A).

2. Make a script to add a new colum for new entries.

---

1.-

In AVG column (column A in the example above) there is an average using the formula:

=QUERY(transpose(query(transpose(B2:$F),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(B2:B),"Avg(Col"&ROW($C2:$C)-ROW($C2)+1&"),",""))), ".\z","")&"")),"Select Col2")

This formula calculates the average of the last 4 weeks only if there's an entry in column B

I need to move this column to the right of the Items list (column B) but when I try to, the formula shows a circular dependency error. Is there a way to tell the formula to only pick the columns I want to?

2.-

There's also a button with an assigned macro to make a new column on the left of the latest week for new entries and insert the week number and month, this is the script:

  function onEdit() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C:C').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('C1').activate()
  .setFormula('=CONCATENATE("Week ",(WEEKNUM(TODAY(),2)-WEEKNUM(EOMONTH(TODAY(),-1)+1)+1)," ",CHOOSE(MONTH(TODAY()),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dec"))');
};

So it becomes something like this:

    + ------ + ------- + ---------- + ---------- + ---------- + ---------- + ---------- +
    |    A   |    B    |      C     |      D     |      E     |      F     |      G     |
    + -------+ ------- + ---------- + ---------- + ---------- + ---------- + ---------- +
    |  AVG   |  ITEMS  | Week X MMM | Week 3 May | Week 2 May | Week 1 May | Week 5 Apr |
    |        |  Item 1 | (NEW WEEK) |    1263    |    1255    |    1142    |     956    |

and this is the formula I am using for the week number:

=CONCATENATE("Week ",(WEEKNUM(TODAY(),2)-WEEKNUM(EOMONTH(TODAY(),-1)+1)+1)," ",CHOOSE(MONTH(TODAY()),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dec"))

The problem with the formula is that uses TODAY() function, which has a variable value while I need a static value. Also when using the script the Conditional Formatting is not carried over in the new column. How can I improve the script?

Answer 1

• For replacing TODAY() with a static value, use the Javascript date methods and format the date according to your spreadsheet setting with Utilities.formatDate()
• Replace the formula part TODAY() with the variable containing the date object as explained above

Sample:

function onEdit() {
  var spreadsheet = SpreadsheetApp.getActive();
  var now = new Date();
  var today = '"' + Utilities.formatDate(now, spreadsheet.getSpreadsheetTimeZone(), "MM/dd/yyyy") + '"';
  spreadsheet.getRange('C:C').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('C1').activate()
   .setFormula('=CONCATENATE("Week ",(WEEKNUM(' +today + ',2)-WEEKNUM(EOMONTH(' + today + ',-1)+1)+1)," ",CHOOSE(MONTH(' + today + '),"Jan","Feb","Mar","Apr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dec"))');
};

• Note: If your spreadsheet date format is no "MM/dd/yyyy" - modify formatDate(date, timeZone, format) accordingly, see here
• The conditional formatting has to be set separately from setFormula, see here for a sample.