Search code examples
dategoogle-sheetsgoogle-apps-script

Get a date value from an array and set it into a cell in Spreadsheet

I'm looking on how to do some actions over arrays with google scripting.

For our school we are creating a tool to automatically creates a spreadsheet with a calendar of a given year with festivities and other special days, some of them repeating across years (New Year, Christmas day, local festivities, etc.), and other variable every year. In this spreadsheet we have a list of every day in the school year, one date per row, to set relevant info in next cells, for example, teachers that doesn't come one day for some reason. This format is useful beacuse we can get this info and change the schedule of the day quickly.

We've think that we can create an array with this unalterable dates (festivities) and then compares with the actual calendar and puts the specific holiday in a cell.

The year in the array doesn't appears because we want to make it useful every year, and if we set now we think there's more tasks to do later. In a previous stage of the script we copy a calendar template with no dates, set the actual year from a users prompt (for instance, "2017", that's what the user enters previously). Maybe this prompt info can be set inside the first value (date) of the array and work every year with a current array with complete date? I have no idea...

The problem appears on the manipulation of dates and conversion to spreadsheet-readable content. Our code, probable with incorrections (I'm not a good programmer yet, I'm learning how to make interaction possible within different javascript elements, and which is the correct sintax for every action, method or class) is as follows.

function writeStableHolidays(){
  var sheet = SpreadsheetApp.getActive().getSheetByName("calendar");
  var stableHolidays = [
    [09/11, "Some Holiday, in format dd/mm"],
    [10/12, "Other Holiday, we don't want to set here the Year"],
    [11/01, "Another one"]
  ];
  var whereToLook = [sheet.getRange(1,1,334,1)] // From here we look the actual year (format DD/MM/YYYY). This sheet has been created automatically from users prompt
  var whereToWrite = [sheet.getRange(1,2,334,2)] // Next cell is where we want to write info from array

  for ( i = 0 ; i < stableHolidays.length ; i++ ) { // Check every item in the array
    for (j = 0 ; j < whereToLook.length ; j++ ) { // Check every first row in the "calendar" sheet
      if (stableHolidays[i][0] == whereToLook[i]) {
        whereToWrite.push(festiusFixes[i][1]); // If there's a coincidence write the holiday info in the cell
      }
    }
  }
}

I detect almost two problems here: a (for now!) unknown syntax incoherence (I said it before this is not yet my speciallity :D ) and some recurrent problem we have on manipulating dates in gscript, different as the treatment in javascript.

Thanks in advance for help.

Solution

  • Assuming your sheet calendar looks like this:

    enter image description here

    You can use next function:

    function writeStableHolidays(){
  var sheet = SpreadsheetApp.getActive().getSheetByName("calendar");
  var stableHolidays = {
    "09/11": "Some Holiday, in format dd/mm",
    "10/12": "Other Holiday, we don't want to set here the Year",
    "11/01": "Another one"
  };

  // get data range
  var dataRange = sheet.getRange('A2:B'+sheet.getLastRow());
  // get data values
  var data = dataRange.getDisplayValues();

  // loop data rows
  for (var i = 0; i < data.length; i++)
  {
    // get DD/MM part from DD/MM/YYYY value
    var dateVal = data[i][0].replace(/\/\d+$/, '');
    // if found holiday on current date
    if (stableHolidays[dateVal])
    {
      // update Holiday column array value
      data[i][1] = stableHolidays[dateVal];
    }
  }

  // write updated values to sheet
  dataRange.setValues(data);
}

    That will update values for Holiday column:

    enter image description here