Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets

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.

Automated Entry Form and Database with Easy/Printable Invoice

Automated Entry Form and Database with Easy/Printable Invoice Using Google Sheets

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.

Community for Data Career Shifters

I needed a network. A network that can understand me.

Who can understand a woman at the age of 40 who will suddenly pivot to an unknown world of data? There are many questions lingering in my head. Is this a wise decision? Will it bring joy and excitement to my career? Will it sustain me financially? But before I can answer those questions for myself I realised that attending courses will not suffice for me to be a Data Analyst. I needed someone to teach me and make me comfortable in every step that I need to take. With that, I looked for a community that has this culture who believes to the capacity of a late career shifter like me. Then, I found SHE LOVES DATA.

2021 I was assigned to be on a group under Singapore Chapter but Richa, Head of Global Operations, reached out and let me to be a part of her team. Inch by inch with guidance from Venetia and trust of Richa I am now managing some of She Loves Data Signature Program. I can know first hand the dates of trainings I can attend and somewhat a free brain picking from top notch instructors of She Loves Data.

SHE LOVES DATA SINGAPORE BASED VOLUNTEERS

Last Thursday (11 August 2022), it is a big in-person event for She Loves Data! The very first Singapore event for 2022. The vibe is wonderful! Quinn and Aimee removed my doubts and gave comfort to alleviate my anxiety. Ohhh.. I want to cry now. They have told me stuffs that makes me think and push away the imposter syndrome out of my head.

The event is CELEBRATE YOU and it clearly stands on its name. I know and I feel that every woman and man in that room have picked some nuggets of wisdom from each other. That we all felt the warmth of community and someone is behind our back to achieve not only our data career goals but also developing ourselves for betterment.

Thank you Jana for building this kind of network.. a network of women that can understand what a career shifter like me is going through. A camaraderie indeed! 
Thank you for the free workshops that enhances and challenges our skills. Thank you so much.

With all that, I can say that I am confident to join the data workforce and let’s crunch those data! *wink-wink*

May ALMIGHTY always bless SHE LOVES DATA and all the volunteers behind it! Onward Sisters!

Exploring the Impact of COVID-19 on the Coaching Industry through Data and Analytics

Coaching Industry: COVID-19 and Beyond

Iam pleased to showcase one of my reports from my internship at Peak Resonance. I am truly grateful to Mr. Jovan Medrano for permitting me to feature this work in my Medium portfolio.

Introduction

Coaching industry was challenged by COVID-19. Face-to-face interaction that is the usual and primary way in delivering coaching services was restricted by most of the government. Thus, coaching hours declined that commonly will result in decrease of income for coaches.

Most coaching organizations lead their members for upskilling and promoting services offered. On that, coaches have pivoted, innovative ways and continue to lend their helping hands for those in need of guidance. This small report will identify coaching pre, during and beyond the crisis of COVID-19.

Coach and Coaching

Many people were confused with the differences of coaches between mentors, or coaches between counselors or psychologists and to begin this paper, we need to distinguish what a coach really is and what they provide.

Coach helps the client to achieve their personal best and to produce the results they want in their personal and professional lives (International Coaching Community). As per International Coaching Federation, coaches do provide objective assessment and observations that foster individual’s or team’s self-awareness and awareness of others, listen closely to fully understand the individual’s or team’s circumstances, act as a sounding board in exploring possibilities and implementing thoughtful planning and decision making, champion opportunities and potential, encouraging stretch and challenge commensurate with personal strengths and aspirations, foster shifts in thinking that reveal fresh perspectives, challenge blind spots to illuminate new possibilities and support the creation of alternative scenarios and maintain professional boundaries in the coaching relationship, including confidentiality, and adheres to the coaching profession’s code of ethics.

The word coaching has been used as a defined procedure only a few decades back (Wildflower, 2013). Coaching is a process focused on working with a person’s needs, wants, goals, or vision for where they want to go, and then designing steps for getting there (Stober & Grant, 2006). On that premise aroused different coaches in different areas of life with diverse approaches.

Coaching Pre-pandemic

Due to the steep expectations from boss, to partner, to health; coaching became popular not only because of recommendations but due to results and goals achieved by the coachee and companies.

Results indicate that individual coaching and group training were effective in reducing procrastination and facilitating goal attainment. Individual coaching created a high degree of satisfaction and was superior in helping participants attain their goals, whereas group training successfully promoted the acquisition of relevant knowledge (Traut-Mattausch, Mühlberger, Jonas, & Losch, 2016).

Thus the global growth and wide spread of coaching profession was evident in 2020 ICF Global Coaching Study:

The coaching continuum: Estimates by world region

Crisis Hit

Everyone was affected when COVID-19 hit and when countries were shut down one by one not only their international borders but also the strict implementation of face-to-face meetings; coaching career deeply felt the impact. Coach practitioners have adjusted their methods used to coach clients. Reflecting the nature of the pandemic, there has been a sharp decrease in coaching in person (80%). Mainly, coach practitioners have increased their use of audio-video platforms (74%) (International Coaching Federation, 2020) and even though digital pivoting was applied, the decrease of numbers of clients were not avoided.

Data Source: (International Coaching Federation, 2020)

In the chart above, 1 of 4 coaches in North America reported no impact neither on income nor employment but Eastern Europe has the greatest financial shock of 85%.

Unlike the 2008 Market Crash where coaching explores in driving financial leaders into transparency and social responsibility to rebuild the brand of their financial organizations (Davis, 2009), 2020 COVID-19 Pandemic is calling for Crisis Leaders in solving cash flow and liquidity, family and friends relationships, employees well-being, managing multiple changes and to prepare for uncertainty (Asia Pacific Alliances of Coaches, 2021).

With that demand, the 2020 survey of the International Coaching Federation gave a 41% average of coaches that has a somewhat positive outlook that their profession will bounce back in 6-months.

Future of Coaching

Blackbyrn blogged that coaching clients are becoming sophisticated and they are willing to pay for coaches with great coaching skills and profound results. In the latest study by Jarosz (2021), the participants of the experiment group noticed positive shifts occurring in their thinking patterns as well, especially in how they evaluate and think about themselves and suggest that the application of coaching in the workplace improves employee well-being and performance.

Data Source: (Asia Pacific Alliance of Coaches, 2020)

Based on 5th Coaching Survey an Asia Coaching Benchmark 2019; Singapore has a relatively more mature coaching market yet some companies find coaching very expensive and not a ‘powerful tool’. Philippines’ growth of coaching services could be attributed to the deeper penetration in existing markets as well as expansion into new markets and industry sectors. In China, coaching is a relatively young concept. One out of three firms planned to use coaching services but the others hindered by lack of awareness, high cost and support from top management. Recent growth in Indonesia’s coaching market reflects expansion opportunities and 5% indicated to adopt coaching in the next 12 months.

Clearly, in every crisis coaches are ready to explore and build new resilient models/tools in helping their clients to face vulnerability, unsafe and uncertain paths. As forecasted, the coaching industry is setting a mark not only in companies but also in different aspects of life.

References:

International Coaching Community. (n.d.). What is Coaching? Retrieved 07 23, 2021, from International Coaching Community: https://internationalcoachingcommunity.com/what-is-coaching/

International Coaching Federation. (n.d.). FREQUENTLY ASKED QUESTIONS. Retrieved 07 23, 2021, from International Coaching Federation: https://coachingfederation.org/faqs

Wildflower, L. (2013). The Hidden History of Coaching. Great Britain: CPI Antony Rowe, Chippenham, Wiltshire .

Stober, D. R., & Grant, A. M. (2006). Evidence Based Coaching Handbook. New Jersey: John Wiley & Sons, Inc.

Traut-Mattausch, E., Mühlberger, M. D., Jonas, E., & Losch, S. (2016, May 03). Comparing the Effectiveness of Individual Coaching, Self-Coaching, and Group Training: How Leadership Makes the Difference.

International Coaching Federation. (2020). COVID-19 and the Coaching Industry. COVID-19 and the Coaching Industry .

Davis, A. A. (2009). Coaching: Navigating the Emerging Trends in Financial Services. IJCO The International Journal of Coaching Organizations .

Asia Pacific Alliances of Coaches. (2021, April). Newsletter. Retrieved Jul 28, 2021, from Asia Pacific Alliance of Coaches — The Coaching Voice of Asia Pacific : http://www.apacoaches.org/news/newsletter/

Blackbyrn, S. (2021, June 11). Blog. Retrieved July 28, 2021, from Coach Foundation: https://coachfoundation.com/blog/30-coaching-trends/

Jarosz, J. (2021, February 01). The impact of coaching on well-being and performance of managers and their teams during pandemic. International Journal of Evidence Based Coaching and Mentoring .

Asia Pacific Alliance of Coaches. (2020). 5th Coaching Survey an Asia Coaching Benchmark, 2019. Asia Pacific Alliance of Coaches.