Google Form Data Validation - Attendance Records using Google Forms and Google Sheets

Question

I'm homeschooling 5 children this year (how am I that old?!). I have tried to streamline the process of attendance for a l.o.n.g. time.

I thought, "I'll use a Google Form that they will simply select their name and the date. (They can't cheat me on attendance because it should match the timestamp. Shhh.) Here is the form: Google Form for Attendance & Google Form for Attendance drop down

The information from the form presents in my Google sheet as: Google Form Data

I have several students that have attendance sheets: Several Student Records

My student record looks like this: Student Record

What I need help with: I need a P to be placed in the Student Record in the correct date as it is validated according to the information in the Google Form Data.

I need this to repeat for all of my student records. To search the data from Google Form Data and put the P in the correct places.

I think I may be making it more difficult than it needs to be, or I need to set up my sheets a bit differently.

Answer 1

You can use Form Submit Installable trigger in Sheets, so that whenever a form was submitted, the script function will be triggered to update the student record and place "P" on the date submitted.

Sample Code (Inbound script in your Google Sheets):

function UpdateStudentRecord(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formValues = e.values;
 
  var name = formValues[1];
  var date = new Date(formValues[2]);

  var sheet = ss.getSheetByName(name);

  //Get Month Year String and the  day
  var timeZone = Session.getScriptTimeZone();
  var dateStr = Utilities.formatDate(date,timeZone, "MMMMM yyyy");
  var day = date.getDate().toString();
  
  //Find the row index of the submitted date 
  var colAValues = sheet.getRange('A1:A').getDisplayValues().flat();
  var rowIndex = colAValues.indexOf(dateStr);

  //Find the column index of the submitted day
  var row3Values = sheet.getRange("A3:3").getDisplayValues().flat();
  var colIndex = row3Values.indexOf(day);

  //Write P in the cell
  //Note that array index is in zero-based while sheet index is in one-based
  sheet.getRange(rowIndex+1, colIndex+1).setValue("P");
}

Pre-requisite (Create a form submit trigger):

What it does?

1. Every time a form was submitted, UpdateStudentRecord() will be executed
2. Get the submitted form values in the event object e.values
3. Get the name of the student and the submitted date
4. Get the submitted date string in "Month Year" format using Utilities.formatDate(date, timeZone, format). And get the day using getDate()
5. Get all columnA values, use Array.flat() to change 2-d array to 1-d array. Search for the date string obtained in step 4 using Array.indexOf()
6. Get all Row3 values, use Array.flat() to change 2-d array to 1-d array. Search for the day obtained in step 4 using [Array.indexOf()]
7. Write "P" on the row and column index obtained in step 5 & 6 using setValue(value)

Note:

In this sample code, the sheet name should match the student name in form's drop-down list.

Output: