I have spearheaded incorporating Google Forms and QR codes to manage our equipment at my day job. As such, we now have a collection of Google Sheets housing data from the initialization of equipment into our tracking system and various updates about it thereafter.
Since we now rely on this data to produce reports for management’s review, we want to ensure the integrity of the data before distributing it. Luckily for us, we can use Google Apps Script to do just that.
Of our Google Sheets, we have two in particular tables of important value:
A sheet containing data that is updated automatically when a form is filed.
A sheet containing data that is manually updated by clerical staff.
//check to make sure equipment ids in form submissions are in admin table
function consensusCheck() {
/* function to get values present in a google sheet by selecting sheet index
in workbook and column index of preference*/
function getValues (sheetNumber, idColumn) {
values = SpreadsheetApp.getActiveSpreadsheet()
.getSheets()[sheetNumber]
.getDataRange().
getValues();
values.shift();
let ids = []
for (let i = 0; i < values.length; i++) {
ids.push(values[i][idColumn]);
}
let uniqueIDs = [...new Set(ids)];
console.log(uniqueIDs.sort((a,b) => {return a-b}));
return uniqueIDs;
}
let formValues = getValues(0, 2);
let adminValues = getValues(1, 0);
// function to check if two arrays are equal
function equalityCheck(arr1, arr2) {
missingIDs = []
if (!(arr1.length === arr2.length)) {
console.log('There is an error.');
}
for (let i = 0; i < arr1.length; i++) {
let val1 = arr1[i]
let val2 = arr2[i]
if (!(val2 === val1)) {
missingIDs.push(val1)
}
}
for (let i = 0; i < missingIDs.length; i++) {
SpreadsheetApp.getUi().alert(`Admin table is missing an entry for ID: ${missingIDs[i]}`);
}
if (missingIDs.length === 0) {
console.log(`The admin table is up to date.`);
}
}
equalityCheck(formValues, adminValues);
}
//special function see apps script docs
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("Custom Menu")
.addItem("Consensus Check","consensusCheck")
.addToUi();
}
By adding this script to sheets we add a custom function menu to our toolbox that clerical staff can run to check if their table contains every piece of equipment listed in the form response table. If the tables DO NOT match, then an error message is raised notifying what piece of equipment needs to be added to clerical’s table.
Below is an example error.
Sheets allows you to add Apps Script right in the window.
Just go to Extensions > Apps Script
When the Apps Scripts editor simply add the Sheets service and write your Apps Script code.
Then just save the code and refresh the tab containing your sheet. Your custom menu will appear.
If you like this content, subscribe to our email list in order to get notified about new post.