Employee review

Rate 1 to 5 (1 = poor, 5 = excellent). HR reviews attendance. Managers and team leads review work and behaviour. Submit adds this review as a new row in the Excel file on Google Drive.

Admin setup (one time) - connect this form to your Google Drive Excel file

In Google Drive create a new Google Sheet (this is the Excel file), then open Extensions > Apps Script.
Delete the default code, click Copy code below and paste it in. Save.
Click Deploy > New deployment > Web app. Execute as: Me. Who has access: Anyone. Deploy and allow access.
Copy the Web app URL (ends with /exec) and paste it below, then click Save URL.
To make the URL permanent for all reviewers, put it in SCRIPT_URL near the bottom of this HTML file before you host it.
Each submit adds a row to that Google Drive file. For the yearly view, run createYearlySummary once in Apps Script.

// Google Apps Script - saves each review as a new row in the Google Sheet
// this script is attached to (Extensions > Apps Script).
// That Sheet is the Excel file in Google Drive.

const SHEET_NAME = "Reviews";

const COLUMNS = [
  ["Submitted at", null],
  ["Year", "year"],
  ["Review month", "month"],
  ["Employee", "employee"],
  ["Employee ID", "empId"],
  ["Team", "team"],
  ["Reviewer", "reviewer"],
  ["Reviewer role", "role"],
  ["Days late (year)", "daysLate"],
  ["Leaves taken (year)", "leaves"],
  ["Attendance regularity", "regularity"],
  ["Punctuality", "punctuality"],
  ["Leave pattern", "leavePattern"],
  ["Overall performance", "performance"],
  ["Delivery", "delivery"],
  ["Thinking", "thinking"],
  ["Quality of work", "quality"],
  ["Technical skill", "technical"],
  ["Communication", "communication"],
  ["Teamwork", "teamwork"],
  ["Ownership", "ownership"],
  ["Learning", "learning"],
  ["Average rating", "average"],
  ["Strengths", "strengths"],
  ["Areas to improve", "improve"],
  ["Other comments", "comments"]
];

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(20000);
  try {
    const data = JSON.parse(e.postData.contents);
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);

    if (sheet.getLastRow() === 0) {
      sheet.appendRow(COLUMNS.map(c => c[0]));
      sheet.setFrozenRows(1);
      sheet.getRange(1, 1, 1, COLUMNS.length).setFontWeight("bold");
    }

    const row = COLUMNS.map(([, key]) => {
      if (key === null) return new Date();
      const v = data[key];
      if (v === undefined || v === "") return "";
      return isNaN(v) ? String(v).replace(/^[=+\-@]/, "'$&") : Number(v);
    });
    sheet.appendRow(row);

    return ContentService.createTextOutput("ok");
  } catch (err) {
    return ContentService.createTextOutput("error: " + err.message);
  } finally {
    lock.releaseLock();
  }
}

// Run this once from the Apps Script editor (select createYearlySummary > Run).
// It makes a "Yearly Summary" tab: average of every rating per employee per year.
function createYearlySummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const name = "Yearly Summary";
  const sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  sheet.clear();
  const col = h => String.fromCharCode(65 + COLUMNS.findIndex(c => c[0] === h));
  const last = col("Average rating");
  const heads = ["Attendance regularity", "Punctuality", "Leave pattern", "Overall performance",
    "Delivery", "Thinking", "Quality of work", "Technical skill", "Communication",
    "Teamwork", "Ownership", "Learning", "Average rating"];
  const select = heads.map(h => "avg(" + col(h) + ")").join(", ");
  const labels = heads.map(h => "avg(" + col(h) + ") '" + h + "'").join(", ");
  const range = "Reviews!A2:" + col("Other comments");
  sheet.getRange("A1").setFormula(
    '=QUERY(' + range + ', "select ' + col("Employee") + ', ' + col("Year") + ', ' + select +
    ' where ' + col("Employee") + ' is not null group by ' + col("Employee") + ', ' + col("Year") +
    ' label ' + col("Employee") + " 'Employee', " + col("Year") + " 'Year', " + labels + '", 0)');
  sheet.setFrozenRows(1);
}

function doGet() {
  return ContentService.createTextOutput("Review form endpoint is running.");
}

Reviewer

Your name Your role

Employee

Employee name Employee ID (optional) Team or department Review month

Select your role above to see the questions for you.

Comments Strengths Areas to improve Other comments