Building an e-Accident Book in 3 Hours with AI
I built this app with AI while eating my breakfast and talking with my wife before I took her to work. So 3 hours is an exaggeration — it was less.
But I should say I had my sophisticated Jotform Accident Record from about 2014 which I created with the help of Jotform and it probably took several days to fine-tune. But it was not a continuous effort and I had help.
I asked Bing Chat AI [Now Copilot] to create the Google App Script code to show my accident record form which I could complete and then when it was received by gmail to upload a link to it to my Google worksheet so a record could be kept of all accident records received.
This is the code (an early draft)
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Open JotForm’, ‘openJotForm’)
.addItem(‘Import Gmail Data’, ‘importGmailToSheet’)
.addToUi();
var rowsAdded = importGmailToSheet();
Logger.log(“Imported “ + rowsAdded + “ rows”);
}
function openJotForm() {
var jotFormUrl = ‘https://form.jotformeu.com/mobileappfactory/accident-record?fbclid=IwAR17HQikmxOGyFNwq9-F1r2a1YB0tObDYXfWHpMhywkoSk6Cbx12GitnDaY';
var html = ‘<script>window.open(“‘ + jotFormUrl + ‘“);google.script.host.close();</script>’;
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, ‘Open JotForm’);
}
function importGmailToSheet() {
// Set up sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Gmail query
var query = ‘in:inbox subject:”*accident record*”’;
// Get Gmail threads
var threads = GmailApp.search(query);
// Loop through threads
threads.forEach(function(thread) {
// Declare msg inside loop
var msg = thread.getMessages()[0];
// Extract data
var from = msg.getFrom();
var subject = msg.getSubject();
// Get message ID
var messageId = msg.getId();
// Construct permalink
var link = “https://mail.google.com/mail/u/0/#inbox/" + messageId;
// Add row to sheet
sheet.appendRow([from, subject, link]);
});
// Return number of rows added
return threads.length;
}
This is the early 3-hour draft but it shows that AI can build workable code using a Jotform which can be accessed via a Google worksheet. It could be improved e.g. extracting the date from the gmail and checking it wasn’t a repeat of an earlier message.
To get it to run the user would click Custom menu and then either Open the Jotform or Import Gmail Data to upload to the accident book worksheet.
I asked MS Copilot in 2024 how this code works and could be improved:
This Google Apps Script code does the following:
- onOpen(): This function is triggered when the Google Spreadsheet is opened. It creates a custom menu with two items: ‘Open JotForm’ and ‘Import Gmail Data’. It also calls the importGmailToSheet() function and logs the number of rows imported.
- openJotForm(): This function opens a JotForm URL in a new window when the ‘Open JotForm’ menu item is clicked.
- importGmailToSheet(): This function imports data from Gmail to the active sheet in the Google Spreadsheet. It searches for emails in the inbox with the subject containing ‘accident record’, extracts the sender’s email address, subject, and a link to the email, and appends this data as a new row in the sheet. It returns the number of rows added.
Here are some suggestions for improving the code:
- Error Handling: Add error handling to manage exceptions that may occur during the execution of the code. For example, the Gmail search may fail, or there may be no active sheet in the Spreadsheet.
- Modularity: The onOpen() function is doing more than just creating a menu. Consider separating the importing of Gmail data to a different function that can be called independently.
- Efficiency: In the importGmailToSheet() function, instead of appending rows one by one, which is time-consuming, consider creating a data array and using the setValues() method to write all data at once.
- Security: Be careful with the openJotForm() function. It opens a URL in a new window, which can be a security risk if the URL is not trusted.
Here’s an example of how you might implement some of these improvements [NOTE THIS IS UNTESTED]:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Open JotForm’, ‘openJotForm’)
.addItem(‘Import Gmail Data’, ‘callImportGmailToSheet’)
.addToUi();
}
function callImportGmailToSheet() {
try {
var rowsAdded = importGmailToSheet();
Logger.log(“Imported “ + rowsAdded + “ rows”);
} catch (e) {
Logger.log(“Error importing Gmail data: “ + e.message);
}
}
function importGmailToSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var query = ‘in:inbox subject:”*accident record*”’;
var threads = GmailApp.search(query);
var data = [];
threads.forEach(function(thread) {
var msg = thread.getMessages()[0];
var from = msg.getFrom();
var subject = msg.getSubject();
var messageId = msg.getId();
var link = “https://mail.google.com/mail/u/0/#inbox/" + messageId;
data.push([from, subject, link]);
});
sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
return threads.length;
}
This revised code includes a separate function callImportGmailToSheet() to call importGmailToSheet() with error handling, and modifies importGmailToSheet() to write all data to the sheet at once for efficiency. Remember to test any changes thoroughly to ensure they work as expected.
