Google-sheets – How to automatically refresh date in Google spreadsheet published to the web

google sheets

I am using Google Spreadsheets to maintain a register of all students in my school and track their monthly payments. Each student has a separate Google spreadsheet maintained and in the spreadsheet I am also calculating their age using their date of birth and now() function. The data is published on the school website.

However, I find that the now() function does not automatically update and save the current date unless some changes are made to the spreadsheet. This means that the data on the web is not refreshed until the spreadsheet has been edited.

How can I make the date to update automatically in the spreadsheet, so that the data published on the web always shows the current date without manually editing and saving the Google spreadsheet?

Best Answer

You can set the interval for recalculation of the spreadsheet in the Spreadsheet settings which you can find in the File menu.

There you can set it to recalculate on change and/or every minute or hour.

Through this, functions like Now() should be updated automatically in the interval you set.

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Here's an Apps Script that will build a second sheet based on the first (javascript-only approach).

I only tested it on a simple 3x5 version of your first set of data, with row 1 (array index 0) being the headers.

It fails if the second sheet already exists - it's not pretty. But it, and the Apps-script tutorials, should get you started.

function buildSheet2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var students = {};  // for a map of students indexed by name, mapping to a string of events separated by "|"
  for(var i=1; i<data.length; i++){
    var eventName = data[i][0];
    Logger.log(eventName);

    for (var j=1; j<3; j++){
      var studentName = data[i][j];
      if (studentName != "") {
        if (!(studentName in students)) {
          students[studentName] = eventName;
        } else {
          students[studentName] = students[studentName] + '|' + eventName;
        }
      }
    }
  }
  Logger.log(students);
  // insert a new sheet and make it active  
  var sNameSheet = SpreadsheetApp.getActive().insertSheet("By student name");
  SpreadsheetApp.setActiveSheet(sNameSheet);
  var newData = [["Name", "Event", "Event", "Event"]];
  var studentKeys = Object.keys(students);
  // split the events for each student into columns
  for (s in studentKeys) {
    var newRow = ["Name", "Event", "Event", "Event"];
    Logger.log (studentKeys[s]);
    newRow[0] = studentKeys[s];
    // clear out temp row
    for (i=1; i<newRow.length; i++) {
      newRow[i] = "";
    }
    var events = students[studentKeys[s]].split("|");
    Logger.log(events);
    for (i=0; i<events.length; i++) {
      newRow[i+1] = events[i];
    }
    newData.push(newRow);
    Logger.log(newRow);
  }
  Logger.log(newData);
  // write data to new sheet
  sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Google-sheets – Published Google Docs spreadsheet: one data set with 3 sheets each sorted differently

Let the raw data be in the first sheet. Then, use the QUERY function to populate the second and the third sheet.
Or, if you need more control, create another sheet for the raw data, and use the QUERY function to populate the first sheet also.

Best Answer

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Google-sheets – Published Google Docs spreadsheet: one data set with 3 sheets each sorted differently

Related Topic