Transfer date specific column from one sheet to another

Question

I have a column that updates daily. I want to copy this column to another sheet on a daily basis, but I have weekly totals every eighth column. How can I continue to create new columns that are not the last column in the sheet as I maintain the weekly total, and create a new weekly total column at the beginning of each week? My current code just adds the new column every day and cannot place the following days of the week between the previous day and the weekly total:

      function DailyKPIs() {
      //var ss = SpreadsheetApp.getActiveSpreadsheet();
      var ss = SpreadsheetApp.openById("1QY-          gVPAOcPbM8MwS77M5fRbBd9yPpxW5WPzpa8KvsKk");
      var target =                     SpreadsheetApp.openById("1axKaf2x5KiqwYh1xNGye67tp1Z1dOezdEi89oOPBcYQ");
      var source_sheet = ss.getSheetByName("KPI Source");
      var target_sheet = target.getSheetByName("Daily");
      var source_range = source_sheet.getRange("C2:C106");
      var last_col = target_sheet.getIndex();
      // add columns only if necessary
      while(target_sheet.getMaxColumns()<=last_col+1){
      target_sheet.insertColumnAfter(last_col)
       }
      var source_values = source_range.getValues();
      // row Nr4, starting on last column+1, 2 columns width and 89-4           rows height
      //var target_range =                                         target_sheet.getRange(4,last_col+1,source_values.length,2);
      var target_range = target_sheet.getRange(1,last_col+1,source_values.length,1);
      target_range.setValues(source_values);
      }

Gerardo Gerardo · Accepted Answer · 2015-09-17T18:06:56

To add new columns that are not the last column in the sheet, you can use "insertColumnAfter" (as you are already doing). That function accepts the index of the column you will have as reference. e.g. If you want to add a new column between the second and third column (column B and C), you can call "insertColumnAfter(2)". The index of column B is 2.

There is also the function "insertColumnBefore()", where you could use the last column index (your var last_col) to insert a new column before the last column.

To add a new column for the weekly total at the beginning of the week, you could use the Date function getDay in a condition. If the day is 1 (monday) then create the new weekly total column.

Transfer date specific column from one sheet to another

1 Answers