Upon finishing Data Analyst Pathway course offered by Development Academy of the Philippines, the next question is what’s next?
I am loud and proud to say that I am one of the pioneer graduate of Project SPARTA and not only that, I am one of their first interns in their Internship Program. A continuity of learning was offered to chosen graduates. Isn’t it nice?
In the SPARTA internship we were grouped into Marketing and Partnership/Events. I was assigned to the latter. My task is to track the partnership’s engagements on their way to signing MOUs, monitor partners communication strategies through their social media and create topics for challenges on Cauayan’s Open Data Hackathon.
One of the challenging parts of being a SPARTA Intern is to understand the problem that the given task is trying to achieve. As a person that is completely from a different background, I need to look into their ideas to understand where they are coming from. And I was blessed to be under Daniel Rosero who does not shush my idea away but almost and always accepts it with compliments.
As always, forever grateful Daniel.
The first task given to me in my SPARTA Internship is to create a form that will showcase everything about the stakeholders. I created two forms: without a database and with an automated entry form/database for an easy and printable report sheet. Remembering that gives me a light bulb moment!
After CCBI’s seminar, I thought to myself that data entry, automation or even digitalization of businesses in the Philippines is still out of reach, it is a long-long way to pave. And it is my light bulb moment to recreate the form I made as SPARTA Intern for an automated entry form/database to replace the manual task of truckers in the Philippines in managing their fleets.
As a former import coordinator of one the largest/oldest haulier companies in Singapore, I am blessed that I saw how powerful it is to have a data entry system to track this kind of business.
This form will give the owners/coordinators the summary of each job and the ease to print out an invoice for their customer.
This is FREE. If you have a google account you can run the script below using an Extension called Apps Script, copy and paste below.
//TO CLEAR THE FORM
function clearForm() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FLEET TRACKER");
const ui = SpreadsheetApp.getUi();
const response = ui.alert("Reset Confirmation", 'Do you want to reset this form?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
sheet.getRangeList([
"C4", "C7", "C9", "C11", "C13", "C15", "C17", "C19", "C21", "C23", "C25", "C28", "C29",
"C30", "C31", "C33", "C34", "C35", "C36", "C37"
]).clearContent().setBackground('#FFFFFF').setBorder(true, true, true, true, false, false);
}
}
//TO DATA VALIDATION FOR DATA ENTRY
function validateEntry(){
var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();
var WMLUserForm = myGooglSheet.getSheetByName("FLEET TRACKER");
var ui = SpreadsheetApp.getUi();
var cellsToCheck = ["C7", "C9", "C11", "C13", "C15", "C17", "C19", "C21", "C23", "C25", "C28", "C29", "C30", "C31", "C33", "C34", "C35", "C36", "C37"];
for (var i = 0; i < cellsToCheck.length; i++) {
var currentCell = WMLUserForm.getRange(cellsToCheck[i]);
currentCell.setBackground('#FFFFFF');
if (currentCell.isBlank()) {
var errorMessage;
switch (cellsToCheck[i]) {
case "C7":
errorMessage = "Please enter BL Number / Booking Number.";
break;
case "C9":
errorMessage = "Select Import/Export.";
break;
case "C11":
errorMessage = "Please enter Client Name.";
break;
case "C13":
errorMessage = "Please enter the Contact Person.";
break;
case "C15":
errorMessage = "Please enter the Mobile Number / Email Address.";
break;
case "C17":
errorMessage = "Please enter Vessel Name / Voyage Number.";
break;
case "C19":
errorMessage = "Please enter Focal Person's Name.";
break;
case "C21":
errorMessage = "Please enter Pick-Up Address (including unit number and postal code), Contact Person and Mobile Number.";
break;
case "C23":
errorMessage = "Please enter Delivery Address (including unit number and postal code), Contact Person and Mobile Number.";
break;
case "C25":
errorMessage = "Please key-in Mobile Number";
break;
case "C28":
errorMessage = "Please select...";
break;
default:
errorMessage = "Please fill out all required fields.";
break;
}
ui.alert(errorMessage);
currentCell.activate();
currentCell.setBackground('#FF0000');
return false;
}
}
return true;
}
//SAVING THE DATA INTO THE DATABASE
function submitData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const formSheet = ss.getSheetByName("FLEET TRACKER");
const dataSheet = ss.getSheetByName("Database");
const ui = SpreadsheetApp.getUi();
const response = ui.alert("Submit", "Do you want to save this data?", ui.ButtonSet.YES_NO);
if (response == ui.Button.NO) return;
if (!validateEntry()) return;
const data = [
formSheet.getRange("C7").getValue(),
formSheet.getRange("C9").getValue(),
formSheet.getRange("C11").getValue(),
formSheet.getRange("C13").getValue(),
formSheet.getRange("C15").getValue(),
formSheet.getRange("C17").getValue(),
formSheet.getRange("C19").getValue(),
formSheet.getRange("C21").getValue(),
formSheet.getRange("C23").getValue(),
formSheet.getRange("C25").getValue(),
formSheet.getRange("C28").getValue(),
formSheet.getRange("C29").getValue(),
formSheet.getRange("C30").getValue(),
formSheet.getRange("C31").getValue(),
formSheet.getRange("C33").getValue(),
formSheet.getRange("C34").getValue(),
formSheet.getRange("C35").getValue(),
formSheet.getRange("C36").getValue(),
formSheet.getRange("C37").getValue(),
new Date(),
Session.getActiveUser().getEmail()
];
dataSheet.appendRow(data);
formSheet.getRangeList([
"C4", "C7", "C9", "C11", "C13", "C15", "C17", "C19", "C21", "C23", "C25", "C28", "C29",
"C30", "C31", "C33", "C34", "C35", "C36", "C37"
]).clearContent().setBackground('#FFFFFF').setBorder(true, true, true, true, false, false);
}
//RETRIEVING DATA FROM THE DATABASE
function searchRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet();
var WMLUserForm= myGooglSheet.getSheetByName("FLEET TRACKER");
var datasheet = myGooglSheet.getSheetByName("Database");
var str = WMLUserForm.getRange("C4").getValue();
var values = datasheet.getDataRange().getValues();
var valuesFound=false;
for (var i = 0; i<values.length; i++)
{
var rowValue = values[i];
if (rowValue[0] == str) {
WMLUserForm.getRange("C7").setValue(rowValue[0]);
WMLUserForm.getRange("C9").setValue(rowValue[1]);
WMLUserForm.getRange("C11").setValue(rowValue[2]);
WMLUserForm.getRange("C13").setValue(rowValue[3]);
WMLUserForm.getRange("C15").setValue(rowValue[4]);
WMLUserForm.getRange("C17").setValue(rowValue[5]);
WMLUserForm.getRange("C19").setValue(rowValue[6]);
WMLUserForm.getRange("C21").setValue(rowValue[7]);
WMLUserForm.getRange("C23").setValue(rowValue[8]);
WMLUserForm.getRange("C25").setValue(rowValue[9]);
WMLUserForm.getRange("C28").setValue(rowValue[10]);
WMLUserForm.getRange("C29").setValue(rowValue[11]);
WMLUserForm.getRange("C30").setValue(rowValue[12]);
WMLUserForm.getRange("C31").setValue(rowValue[13]);
WMLUserForm.getRange("C33").setValue(rowValue[14]);
WMLUserForm.getRange("C34").setValue(rowValue[15]);
WMLUserForm.getRange("C35").setValue(rowValue[16]);
WMLUserForm.getRange("C36").setValue(rowValue[17]);
WMLUserForm.getRange("C37").setValue(rowValue[18]);
return; //come out from the search function
}
}
if(valuesFound==false){
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}
}
//EDITING RECORD
function editRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet();
var WMLUserForm= myGooglSheet.getSheetByName("FLEET TRACKER");
var datasheet = myGooglSheet.getSheetByName("Database");
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Submit", 'Do you want to update the data?',ui.ButtonSet.YES_NO);
if (response == ui.Button.NO)
{return;
}
var str = WMLUserForm.getRange("C4").getValue();
var values = datasheet.getDataRange().getValues();
var valuesFound=false;
for (var i = 0; i<values.length; i++)
{
var rowValue = values[i];
if (rowValue[0] == str) {
var iRow = i+1; //identify the row number
datasheet.getRange(iRow,1).setValue(WMLUserForm.getRange("C7").getValue());
datasheet.getRange(iRow,2).setValue(WMLUserForm.getRange("C9").getValue());
datasheet.getRange(iRow,3).setValue(WMLUserForm.getRange("C11").getValue());
datasheet.getRange(iRow,4).setValue(WMLUserForm.getRange("C13").getValue());
datasheet.getRange(iRow,5).setValue(WMLUserForm.getRange("C15").getValue());
datasheet.getRange(iRow,6).setValue(WMLUserForm.getRange("C17").getValue());
datasheet.getRange(iRow,7).setValue(WMLUserForm.getRange("C19").getValue());
datasheet.getRange(iRow,8).setValue(WMLUserForm.getRange("C21").getValue());
datasheet.getRange(iRow,9).setValue(WMLUserForm.getRange("C23").getValue());
datasheet.getRange(iRow,10).setValue(WMLUserForm.getRange("C25").getValue());
datasheet.getRange(iRow,11).setValue(WMLUserForm.getRange("C28").getValue());
datasheet.getRange(iRow,12).setValue(WMLUserForm.getRange("C29").getValue());
datasheet.getRange(iRow,13).setValue(WMLUserForm.getRange("C30").getValue());
datasheet.getRange(iRow,14).setValue(WMLUserForm.getRange("C31").getValue());
datasheet.getRange(iRow,15).setValue(WMLUserForm.getRange("C33").getValue());
datasheet.getRange(iRow,16).setValue(WMLUserForm.getRange("C34").getValue());
datasheet.getRange(iRow,17).setValue(WMLUserForm.getRange("C35").getValue());
datasheet.getRange(iRow,18).setValue(WMLUserForm.getRange("C36").getValue());
datasheet.getRange(iRow,19).setValue(WMLUserForm.getRange("C37").getValue());
datasheet.getRange(iRow, 22).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
datasheet.getRange(iRow, 23).setValue(Session.getActiveUser().getEmail());
ui.alert(' "Data updated for - Emp #' + WMLUserForm.getRange("C7").getValue() +' "');
WMLUserForm.getRange("C4").clear();
WMLUserForm.getRange("C7").clear();
WMLUserForm.getRange("C9").clear();
WMLUserForm.getRange("C11").clear();
WMLUserForm.getRange("C13").clear();
WMLUserForm.getRange("C15").clear();
WMLUserForm.getRange("C17").clear();
WMLUserForm.getRange("C19").clear();
WMLUserForm.getRange("C21").clear();
WMLUserForm.getRange("C23").clear();
WMLUserForm.getRange("C25").clear();
WMLUserForm.getRange("C28").clear();
WMLUserForm.getRange("C29").clear();
WMLUserForm.getRange("C30").clear();
WMLUserForm.getRange("C31").clear();
WMLUserForm.getRange("C33").clear();
WMLUserForm.getRange("C34").clear();
WMLUserForm.getRange("C35").clear();
WMLUserForm.getRange("C36").clear();
WMLUserForm.getRange("C37").clear();
WMLUserForm.getRange("C4").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C7").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C9").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C11").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C13").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C15").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C17").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C19").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C21").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C23").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C25").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C28").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C29").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C30").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C31").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C33").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C34").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C35").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C36").setBorder(true, true, true, true, false, false);
WMLUserForm.getRange("C37").setBorder(true, true, true, true, false, false);
valuesFound=true;
return;
}
}
if(valuesFound==false){
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}
}You can comment here or email me to send you a copy of this form or if you want me to customize some parts just for you.
This is what being an analyst is, analysts are not just all about data wrangling, or creating a dashboard or detecting trends and patterns. Analysts are here to help solve real-life problems using available (free or paid) tools for the betterment of the businesses that we can pass to next generations.
