The 360-degree feedback system
As part of our internal innovations, we created a feedback system where employees can evaluate the work and communication of their colleagues every three months. They can also describe any problems or observations they might have—either anonymously or by name. The feedback helps clarify misunderstandings, improves communication and team cohesion, and evaluates individual contributions.
We initially set up this system within the G-Suite environment, and about half a year later we built our own Horizont system. Naturally, in this article I will present the former.
I will not go into detail about the background, the questions asked, or the overall structure of the 360-degree system, as it is irrelevant for the purpose of this article. Let’s assume that we created Google Form templates in four categories:
- developer
- lead developer
- manager
- office
Each of these Forms has a different structure with shared as well as unique questions. Each employee receives the template corresponding to their category.
For every employee, an individual copy is generated from the appropriate Form, and all related information is sent to them in a personalized email. The email also includes the public link to the Form. Each employee then sends their Form to others via their preferred communication channel. The rule was that by the end of the evaluation period, everyone must have at least three submissions—excluding their own. This allows them to compare their self-assessment with external feedback.
Implementation
I. Creating initial data
1. First, create template Forms for the desired categories. Google Forms’ editor interface is perfectly suitable for entering competencies—no coding required.
A few suggestions to prevent misuse—configure the Forms as follows:
- permit only users within the domain to fill them out
- do not collect email addresses
- allow only one submission per person
Domain verification and anonymity together mean authentication is required to confirm domain membership, but no user data is attached.
2. Create a Spreadsheet listing all participants involved in the evaluation.
At minimum, we need the following data:
- email address for notifications
- name for personalized greetings
- category—the Form template they should receive
A Spreadsheet is easy to extend or modify later. Although, in true SYOC style, the ideal solution would be using G-Suite organizational users so this too could be automated. :)
II. Writing the script
1. Start by reading the Spreadsheet containing the employee list. Open it, iterate through the rows, and extract the first three columns. Begin reading from index 2 (var i=2) because numbering starts at 1, and we also have a header row.
function listEmployee(spreadsheetId) {
var employeeSpreadsheet = SpreadsheetApp.openById(spreadsheetId);
var employeeSheet = employeeSpreadsheet.getSheets()[0];
var employeeRange = employeeSheet.getRange("A:C");
var lastRow = employeeSheet.getLastRow() + 1;
var result = [];
for (var i = 2; i < lastRow; i++) {
var email = employeeRange.getCell(i, 1).getValue();
var name = employeeRange.getCell(i, 2).getValue();
var category = employeeRange.getCell(i, 3).getValue();
result.push({
email: email,
name: name,
category: category
});
}
return result;
}Now we have each participant’s name, email address, and category.
2. Next, retrieve all Form templates from the designated folder that match the employee categories.
function listTemplates(sourceFolder, subFolderName) {
var templateFolderIterator = sourceFolder.getFoldersByName(subFolderName);
var templateFolder = templateFolderIterator.next();
var subFolders = templateFolder.getFiles();
var result = {};
while (subFolders.hasNext()) {
var file = subFolders.next();
var mimeType = file.getMimeType();
if (mimeType.indexOf("google-apps.form") > 0) result[file.getName()] = file;
}
return result;
}
3. With our initial data ready, iterate through the employee list and create a folder plus the required Form for each person.
var employees = listEmployee(EMPLOYEES_SPREADSHEET_ID);
var templates = listTemplates(appFolder, "Template");
// Create all form user by user
employees.forEach(function(emp) {
if (templates.hasOwnProperty(emp.category)) {
/*
A) create employee's folder and share silently
B) generate form
C) send email to employees
*/
}
});Let’s break down the steps inside the loop.
A) Create the employee’s folder and share it:
// Create user folder
var employeeFolderName = generateFolderName(emp.name, PERIOD);
var employeeFolder = periodFolder.createFolder(employeeFolderName);
// SHARE 1: share silently with employee
employeeFolder.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.VIEW);
Drive.Permissions.insert(
{ role: "reader", type: "user", value: emp.email },
employeeFolder.getId(),
{ sendNotificationEmails: false, supportsTeamDrives: false }
);
Logger.log("Sharing folder done.");
B) Generate the personalized Form:
// Make copy from template
var employeeTemplateFile = templates[emp.category];
var employeeFormFilename = generateFilename(emp.name, PERIOD);
var employeeFormFile = employeeTemplateFile.makeCopy(
employeeFormFilename,
employeeFolder
);
Logger.log("Form copy done: " + employeeFormFilename);
// Update new form using employee's data
var employeeForm = FormApp.openById(employeeFormFile.getId());
var formTitle = generateTitle(emp.name, PERIOD);
employeeForm.setTitle(formTitle);
Logger.log("Form updated for " + emp.name);
// Make copy from template spreadsheet
var employeeFeedbackFilename = generateFeedbackFilename(emp.name, PERIOD);
var employeeFeedbackFile = templateFeedbackFile.makeCopy(
employeeFeedbackFilename,
employeeFolder
);
var feedbackTitle = generateFeedbackTitle(emp.name, PERIOD);
employeeFeedbackFile.setName(feedbackTitle);
Logger.log("Spreadsheet copy done: " + employeeFeedbackFilename);
// Bind spreadsheet to form
employeeForm.setDestination(
FormApp.DestinationType.SPREADSHEET,
employeeFeedbackFile.getId()
);
Logger.log("Spreadsheet bind to form done.");
// SHARE 2: revoke to show feedback file
Utilities.sleep(100); // wait before remove
var feedbackFileUserPermission = findPermissionForUser(
employeeFeedbackFile.getId(),
emp.email
);
if (feedbackFileUserPermission && feedbackFileUserPermission.role == "reader") {
Drive.Permissions.remove(
employeeFeedbackFile.getId(),
feedbackFileUserPermission.id
);
}
Logger.log("Removed viewer permission for feedback file");
C) Sending the email:
If we’ve come this far, we may as well send a nicely formatted HTML email. The following trick works well in G-Suite: write and format your email in Google Docs, then convert it to HTML using an external API call.
function convertToHTML(docId){
var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
var url = "https://docs.google.com/feeds/download/documents/export/Export?id=" + docId + "&exportFormat=html";
var param = {
method : "get",
headers : {"Authorization": "Bearer " + ScriptApp.getOAuthToken() },
muteHttpExceptions:true,
};
var html = UrlFetchApp.fetch(url,param).getContentText();
return html;
}
Before entering the main loop, read the email template once using the above function:
var body = DocumentApp.openById(BODY_DOCS_ID).getBody().getText();
var bodyHTML = convertToHTML(BODY_DOCS_ID);
Then use it for each email:
Logger.log("Employee folder: " + employeeFolder.getUrl());
Logger.log("Published url: " + employeeForm.getPublishedUrl());
Logger.log("Summary url: " + employeeForm.getSummaryUrl());
var userPublishedUrl = employeeForm.shortenFormUrl(
employeeForm.getPublishedUrl()
);
var employeeFolderUrl = employeeFolder.getUrl();
var userBody = body
.replace(/{{PUBLISHED_URL}}/g, userPublishedUrl)
.replace(/{{EMPLOYEE_FOLDER}}/g, employeeFolderUrl);
var userBodyHTML = bodyHTML
.replace(/{{PUBLISHED_URL}}/g, userPublishedUrl)
.replace(/{{EMPLOYEE_FOLDER}}/g, employeeFolderUrl);
MailApp.sendEmail({
to: emp.email,
subject: SUBJECT + " - " + PERIOD,
body: userBody,
htmlBody: userBodyHTML,
noReply: true
});
And with that, the core system is complete. From here, you could implement company-wide evaluation logic based on the submitted data—but I leave that to the reader.
How could it be improved?
Anyone who has worked in software development knows that you never truly finish a program—only stop working on it. This applies here as well.
You could write another Script that iterates through all Forms and toggles their availability. This way, you can centrally manage when feedback can be submitted.
These steps could even be scheduled: automatically send out Forms every three or six months, and close them a week later. Even better: send a reminder two days before the deadline.
The earlier mentioned integration of G-Suite organizational users would also be worth implementing.
I hope this provided an interesting use case. We looked at Spreadsheet handling, Form editing, and email sending. If you want an example involving scheduling, I recommend continuing the series.