Script to Pull Data from Daily Data Sheets to One Summary Sheet Within the Same Workbook

Question

Here is a copy of the document I'm working on: https://docs.google.com/spreadsheets/d/1amIjjQsA8sNKf7mZvE1GjsF-377YOWxVIRk6HRgQjfA/edit?usp=sharing

Every day we (my non-profit school) duplicate the template document into a new sheet with that day's date on it. Each day, 5 points of data need to be transferred from the daily sheet into the "progress notes" summary sheet (total score, notes, character dollars, time out of classroom and independence level). My goal is to create a script that will automatically push those 5 points of data from the daily sheet to the summary sheet OR pull the 5 points of data from the daily sheet to the summary sheet. The information should align between the two sheets by date.

This is what I've gotten to so far:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Send Data to Progress Notes', functionName: 'onEdit'}
  ];
  spreadsheet.addMenu('Progress Notes', menuItems);}

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A1:L39");
  var cell = range.getActivecell(19, 12).getValues();
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Progress Notes");
  target.getRange("L19").setValue(cell.getValue());
  }

To be honest, I have very little idea what I'm doing and am just exploring (google!) with trial and error until I get something to work. I'm a special ed math teacher by trade, but have a vision for efficient data collection--so I'm giving it a shot!

Thanks for any help you can offer!

Answer 1

function Dailypush() {
  var d = new Date(); //get Today's Date
  d = Utilities.formatDate(d,"GMT","MM/dd/YYYY");
  //d = (d.getMonth()+1)+'/'+d.getDate()+'/'+d.getFullYear(); //Format it to mm/dd/yyyy
  var ss = SpreadsheetApp.getActive();
  var ssh = ss.getSheetByName(d); //Get today's sheet (source sheet)
  var rng = ["M20","A31","M25","M11","M12"]; //5 Data points
  rng = rng.map(function gR(e) { return ssh.getRange(e).getValue();}); //Get Value of 5 data points
  var dsh = ss.getSheetByName('Progress Notes'); //Get destination sheet
  rng.unshift(d); //Add date to the 5 data points
  //Logger.log(rng);
  dsh.appendRow(rng); //Add the 6 data points to the last row
}

Add a TimeTrigger to add Data daily from source sheet to destination sheet