Track Laptop Asset Handover using Google Apps Script

Introduction

We use Google Apps Script because some companies use Google Workspace (formerly G Suite) for their day-to-day work, and it cost nothing to set up as there is no hosting and annual maintenance fees. The authentication is seamless as staff would have already logged on to their Google account in Chrome to check their work Gmail. Everybody is familiar with creating new Google Forms and the Drive security sharing options for Google Sheets.

Background

Any IT department should have two essential systems are the support ticketing system and the asset tracking list. A ticketing system allows end-users to log issues that they have with their laptops, printers, and software, thus enables IT to follow up closely, and when it comes to year-end appraisal, they have something to present to their boss. But today, we will cover the asset tracking list. Every year, there will be an IT audit, and the auditors will pick laptops randomly from an asset list, and IT needs to bring them to the owner or IT store to verify its presence. This asset list also helps the IT team know how many spare laptops are and what models are available for issuance.

Prerequisites

There is no need to subscribe to Google Workspace to attempt this tutorial as the minimum requirement is a free personal Google account.

• Google Forms to create a one-page laptop handover form.
• Google Sheets to collect and review form responses.
• Google Apps Script, which is based on JavaScript language.

Step 1 – Create a Laptop Issue Form using Google Forms

Log into your Google account, go to Forms and start a new form. Change the ‘Untitled form’ to, for example, ‘Laptop Handover Form’. Form description is optional. Add the questions below to the form, but you can modify them as you deem fit.

1. Issue or Return? – Multiple choice (required)
   1. Issue
   2. Return
2. Laptop Model – Multiple choice (required)
   1. Dell XPS 13
   2. MacBook Pro (16-inch, 2019)
   3. Acer Swift 3 (2020, AMD Ryzen 7 4700U)
3. Laptop Serial No. – Short answer test (required)
4. Computer Accessories & Peripherals – Checkboxes
   1. External monitor
   2. USB keyboard
   3. USB mouse
   4. Extra charger adapter
5. Staff ID – Short answer text (required)
6. Staff Name – Short answer text (required)
7. Staff Department – Dropdown (required)
   1. Design
   2. Engineering
   3. Finance
   4. Human Resource
   5. Digital Transformation

Step 2 – Test Form, Check Responses and Import Laptop Serial No.

Click the eye-like symbol at the top right to Preview the form in another browser tab. Test the setup by completing the form with different options each time and when you are happy with the arrangement, go back to the previous tab (form edit mode). Click on the Responses link located at the mid-top of the form and View responses in Sheets. The form will record all the answers in the default ‘Form Responses 1‘ sheet.

Open a new sheet (next to Form Responses 1) and rename it to, e.g. ‘Master Asset’. Create a table and import all the laptop models and serial numbers from your company laptop asset list. You may also fill in the Staff ID, Name and Department for existing owners of these laptops but leave them empty for available free-to-loan laptops in your IT store.

Step 3 – Code the Workflow!

Go to Tools > Script editor and open the Apps Script editor interface. Copy and paste the below function onFormSubmit() below and click Save project.

function onFormSubmit() {
  var spreadsheet = SpreadsheetApp.getActive();
  var responseSheet = spreadsheet.getSheetByName('Form Responses 1');

  // Return the position of last row that recorded last entry
  var rLastRow = responseSheet.getLastRow();
  // Return the position of last column that recorded last entry
  var lastCol = responseSheet.getLastColumn();
  // Store values to array values
  var values = responseSheet.getRange(rLastRow, 1, 1, lastCol).getValues()[0];

  var timeStamp = values[0];
  var issueReturn = values[1];
  var laptopModel = values[2];
  var serialNo = values[3];
  var staffID = values[5];
  var staffName = values[6];
  var staffDept = values[7];

  // Replace sheet name accordingly
  var copyToSheet = spreadsheet.getSheetByName('Master Asset');
    
  var data = copyToSheet.getDataRange().getValues();

  // Find row by row the entered serial no.
  for (var i = 0; i < data.length; i++) { // i = row
    // Column B = Laptop Serial No.
    if (data[i][1] == serialNo) {
      Logger.log((i+1));

      // Condition: Issue
      if (issueReturn == 'Issue') {
        // Replace user name and i+1 because spreedsheet 1st row = 1 but i = 0
        copyToSheet.getRange('C' + (i+1)).setValue(staffID);
        copyToSheet.getRange('D' + (i+1)).setValue(staffName);
        copyToSheet.getRange('E' + (i+1)).setValue(staffDept);
      }

      // Condition: Return
      if (issueReturn == 'Return') {
        copyToSheet.getRange('C' + (i+1)).setValue("");
        copyToSheet.getRange('D' + (i+1)).setValue("");
        copyToSheet.getRange('E' + (i+1)).setValue("");
      }

      SpreadsheetApp.flush(); // IMPORTANT to apply all changes right away
    }
  }
}

1. Use the Active spreadsheet – Laptop Handover Form (Responses)
2. Use the sheet – Form Responses 1.
3. Get the last row values and store them in an array, for example, column A = values[0] and column B = values[1].
4. Assign the array values to variable names for user-friendly coding.
5. Now switch to the sheet – Master Asset
6. Loop row by row to find matching Laptop Serial No. entry.
7. If handover is Issue, copy the form values (Staff ID, Name, Department) to (i+1)th row.
8. Else handover is Return, empty/blank the associated cell values (Staff ID, Name, Department) instead.

Step 4 – Add Email Notification on Success/ Failure (optional)

It will be great if the laptop serial from Google form can be validated against the laptop serials in the Master Asset spreadsheet. Or vice versa, the laptop serials from the Master Asset spreadsheet can be populated into Google form as drop-down options. However, a little research on the Internet shows that this is not a straightforward resolution and will require some effort. To keep things simple, we will add an email notification to inform you (or the IT team) that the handover is a success or failure depending on whether the laptop serial existed in the Master Asset sheet. After that, we can rectify any invalid laptop serial numbers manually.

function onFormSubmit() {
  // Step 3 code here

  // For email notification (default unsuccessful unless serial no. found)
  var updateStatus = 'unsuccessful';

  for (var i = 0; i < data.length; i++) {
    if (data[i][1] == serialNo) {
      // Step 3 code here

      // For email notification (update status when serial no. found)
      var updateStatus = 'successful';
    }
  }

  // Send email using function emailIT()
  emailIT(issueReturn, laptopModel, serialNo, updateStatus);
}

function emailIT(issueReturn, laptopModel, serialNo, updateStatus) {
  var htmlBody = 'Dear IT team,';
  htmlBody += '<p>This is to notify that the recent laptop ' + issueReturn + ' is ' + updateStatus + '.</p>';
  htmlBody += '<b>Model:</b> ' + laptopModel;
  htmlBody += '<br><b>Serial no.:</b> ' + serialNo;
  htmlBody += '<p>Details extracted from <a href="https://docs.google.com/spreadsheets/d/<spreedsheetLinkID>/edit" target="_blank">Laptop Handover Form (Responses)</a></p>';
  
  // Replace email xyz@example.com to target recipient
  GmailApp.sendEmail('xyz@example.com', 'Laptop Handover Report', '', {htmlBody:htmlBody});
}

GmailApp.sendEmail will trigger an example of the email below to be sent out. The possible message is “This is to notify that the recent laptop [Issue / Return] is [unsuccessful / successful]. Since this is an HTML-enabled email, we can apply font style or color to it.

Conclusion

So in a single spreadsheet, we have Form Responses 1 sheet that logs all the handover transactions. We can use it to trace a particular laptop back to its previous or current owner for audit purposes. Next, we have the full Master Asset sheet of all loaned out and free laptops, which helps pick a suitable notebook for onboarding a new staff. Also, when a staff exits, using the same form with ‘Return’ selected will set all the staff details to blank for that returned laptop, making it available (free) again. According to the organization’s requirements, we can modify the questions and answers in the handover form and add columns (e.g., RAM, CPU, and year purchased) to the Master Asset sheet. Since all the records are stored in spreadsheet format, one can eventually migrate them to a full-fledged asset management software.