Google Sheets Calendar

Question

I would like some help in pulling certain data from a google form linked to a spreadsheet and set a certain value in the date of a certain employee. For example if he/she marked as Vacation Leave then the letter V will be marked under the Date. I have attached the link to my calendar to give an example, I have tried searching the net and this is my second time posting here.

I started the Code but I am stuck in actually finding the Cell for a certain date and inserting the letter.

https://docs.google.com/spreadsheets/d/1uFTR2_B7T0QBr7fTflByFffmmiszbNj_RaCvLEfYRCA/edit?usp=sharing

function setData(){
  //Spreadsheets
  var ss = SpreadsheetApp;  
  var data = ss.getActiveSpreadsheet().getSheetByName("Data");
  var calendar = ss.getActiveSpreadsheet().getSheetByName("Calendar");

  //Get last row / Individual Cells from Form
  var lRow = data.getLastRow();
  var lRowData = data.getRange(lRow,1,1,17).getValues();
  var approval = data.getRange(lRow,17,1,1).getValue();  
  var leave = data.getRange(lRow,9,1,1).getValue(); 
  var agentName = data.getRange(lRow, 5,1,1).getValue();
  var dateBefore = data.getRange(lRow, 10,1,1).getValue();
  var dateAfter = data.getRange(lRow, 11,1,1).getValue();

  //Calander Variable Arrays
  var allDates = calendar.getRange("LA1:NJ1").getValues();
  var allNames = calendar.getRange("A4:A160").getValues();

  for(var i = 0; i<allNames.length; i++){
    if (approval === "Approved" && allNames[i][0] === agentName){

   //Here I need it to insert the dates under the correct name and date with a value of V   H   S   M   U   T.


    };

  };
};

Tedinoz Tedinoz · Accepted Answer · 2019-11-06T08:08:19

You are building a spreadsheet-based Leave Calendar based on information from a form response. Based on your existing Calendar, you are having problems identifying the relevant leave dates, and then filling calendar cells to indicate proposed leave.

The problem is there are no fields in rows 1,2 or 3 of Calendar that have the same date format as the Start Date and End Date fields on Form. As a result, there's no easy way to search for a match of the form data fields.

The solution, in my view, is to change the format of the date fields in rows 2 and 3 and enable a search to be match.

Row 2

the existing format is "d" - day of the month (Numeric)
change the format to match the Form Start/End dates: "d-MMM-yyyy".
the font colour for this field can be used to "hide" these dates, and the column width reduced also.

Row 3

the existing format is "E" - day of the week (Name)
change the format to combine the existing formats of rows #2 and #3 - "E-d"

Aspects of the script

the form data is retrieved as getDisplayValues(). The purpose of this is to get the date as a string to facilitate the search.
Two sets of Calendar data are obtained
1) the dates row (Row#2)
2) the names column (Col #1). The Javascript map method is used to convert names from a 2D array to a 1D array. This creates an array that can be easily searched.
the Javascript indexOf method is used to find the Column match for the start and end dates, and to match the name in the Calendar Column (which yields the Row)
the script loops through the number of days leave to create a temporary array of "V" values.
using the row number and the start and end column numbers, a range can be defined on calendar and then updated from the values in the temporary array.

Presumably your function would be triggered by onFormSubmit(e).

Form data

Calendar - Before

Calendar - After

function so5871726503(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var form = ss.getSheetByName("Form");
  var calendar = ss.getSheetByName("Calander");

  //get form data
  var formdataRange = form.getRange(6,1,1,9);// test data
  var formData = formdataRange.getDisplayValues(); // display values to format date as string

  //get the employee name, start date and end date
  var formName = formData[0][1];
  var formSD = formData[0][3];
  var formED = formData[0][4];
  // Logger.log("DEBUG: name = "+formName+", start date = "+formSD+", end date = "+formED);

  //get Calendar variables
  var calLR = calendar.getLastRow();
  var calLC = calendar.getLastColumn();
  var caldateStart = 9;
  var calnameStart=4;

  // get Calendar dates
  var calDateRange = calendar.getRange(2,caldateStart,1,calLC-caldateStart+1);
  var calDateValues = calDateRange.getDisplayValues();

  // get Calendar names 
  var calNameRange = calendar.getRange(calnameStart,1,calLR-calnameStart+1);
  var calNameValues = calNameRange.getValues();
  var calNames = calNameValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]

  // there should be some error checking on indexof results = -1 in case there is a mismatch.

  // find form start date in calender
  var startdateresult = calDateValues[0].indexOf(formSD);
  var startdateresultcol = startdateresult+caldateStart;  
  // Logger.log("DEBUG: start date result = "+startdateresult+", column = "+startdateresultcol);

  // find form end date in calender
  var enddateresult = calDateValues[0].indexOf(formED);
  var enddateresultcol = enddateresult+caldateStart;  
  // Logger.log("DEBUG: end date result = "+enddateresult+", column = "+enddateresultcol);

  // find form name in calender
  var nameresult = calNames.indexOf(formName);
  var nameresultrow = nameresult+calnameStart;  
  // Logger.log("DEBUG: name result = "+nameresult+", row = "+nameresultrow)

  // calculate number of days leave
  var daysleave = enddateresultcol-startdateresultcol+1
  // Logger.log("DEBUG: days leave = "+daysleave)

  // create array variable to hold leave data
  var leave=[];

  // loop to create data to fill Calendar
  for (i=0;i<daysleave;i++){
    leave.push("V");
  }
  // Logger.log(leave); // DEBUG

  // build leave range
  var calLeave = calendar.getRange(nameresultrow,startdateresultcol,1,daysleave);
  //Logger.log(calLeave.getA1Notation()); //DEBUG

  // Update the leave range
  calLeave.setValues([leave]);

}

Google Sheets Calendar

1 Answers