Capture data into a new tab before script clears columns (Google Sheets)-CodePudding

A really simple one I think, but I would appreciate any help you can give.

In Google Sheets I have in A7:A a list of unique users taken from (F7:F) which can vary from 0 to 20 =unique(filter(F7:F,F7:F<>""))

In B7:B I keep a tally of how many checkboxes are ticked (G7:G) for those unique users example: =COUNTIFS($G$7:$G,TRUE,$F$7:$F,A7)

I also have a button/script which clears most cells and is used at the end of each day. for arguements sake lets say it clears users (F) and the checkbox (G).

What I need is for the button to also capture the information in A7:B? BEFORE it clears F and G and output it to a new tab, and as this happens each day I would like for it to not overwrite the previous days capture.

Thanks in advance for any assistance you can provide.

CodePudding user response：

Try this

function dailyReport(){
  var doc=SpreadsheetApp.getActiveSpreadsheet()
  var mainTab = doc.getSheetByName('myMainTab')
  var row = mainTab.getRange('A7').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
  var newTab = doc.getSheetByName('myNewTab')
  var data = mainTab.getRange('A7:B' row).getValues()
  data=transpose(data)
  newTab.getRange(newTab.getLastRow() 1,1,data.length,data[0].length).setValues(data)
}
function transpose(a){
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

you will get raw values on 2 lines.

If you want to do statistics, prefer

function dailyReport(){
  var doc=SpreadsheetApp.getActiveSpreadsheet()
  var mainTab = doc.getSheetByName('myMainTab')
  var row = mainTab.getRange('A7').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
  var newTab = doc.getSheetByName('myNewTab')
  var data = mainTab.getRange('A7:B' row).getValues()
  var d=new Date()
  row = newTab.getLastRow() 1
  newTab.getRange(row,2,data.length,data[0].length).setValues(data)
  newTab.getRange(row,1,data.length,1).setValue(d)
}