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");

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!


1 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
  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

  • Thank you very much! This is exactly on the correct track. I'm encountering an error: Cannot call method "getRange" of null for this line: rng = rng.map(function gR(e) { return ssh.getRange(e).getValue();}); //Get Value of 5 data points Any tips? I put in test data so the 5 data points all had a value, but I'm unsure where to go from there. Mar 8, 2018 at 19:21
  • Is it possible to modify this line in such a way that it pulls the date from cell J1 which is were we mark which day that data applies to? This way if we copy the 5 points of data over, it pulls the date from the sheet and not the current date? rng.unshift(d); //Add date to the 5 data points Mar 8, 2018 at 21:09
  • The title of the sheet will match the date in J1. I think the script as it is will be fine. I was initially thinking about having a manual trigger to send active sheet to progress notes pulling date from J1, but then we could end up with multiple entries for the same day. I think its best to leave it as is. I already have it set for the daily pull. Thanks! Mar 8, 2018 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.