Writing an Apps Script to cross reference tables in Sheets
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.
Use case: Consensus check
Of our Google Sheets, we have two in particular tables of important value:
- Form Responses
- Admin table
Form Responses
A sheet containing data that is updated automatically when a form is filed.
Admin table
A sheet containing data that is manually updated by clerical staff.
Problem: What if a form response entry is made but the clerical staff isn't notified?
Solution: Add a custom menu to allow for consensus check between tables.
//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();
}
What does this do for Google Sheets?
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.
How to add this functionality to sheets, for those curious...
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.
