Google Sheets Report Automation

Project Overview

The client had a spreadsheet that was updated daily with information on travel bookings (accommodations, arrival and departure times, per diem, contact details, insurance, etc.). Each trip had a unique ID.

The client needed to create a preview of the data in the same spreadsheet in order to be able to evaluate each trip separately. In addition, they needed a way to automatically create a new report on demand by specifying the code of a single trip.

As an additional element, the reports created had to remain linked to the master spreadsheet, so that any changes in the source information would be automatically reflected in the new document.

Provided Solution

1 - A tab was created that enables the Master Spreadsheet query, cleans the data and applies the desired formatting. The result shows a preview of how the new document would look like in case you want to generate it.

2 - An automation using AppsScript was devised to create a new Spreadsheet and, by using functions such as QUERY() and IMPORTRANGE() (among others), the necessary information is linked to the Master Spreadsheet.

3 - The result is a new file completely linked to the Master Spreadsheet, so that any change in a record of the indicated trip is automatically reflected in the new document.

4 - Some additional controls were also implemented, such as the review of existing documents on the same trip and the verification of the correct format of the data.

Some Example AppsScript Functions

This function automates the creation of a structured Google Spreadsheet for a specific trip or flight, with multiple tabs pre-populated and formatted based on a master template. It ensures that no duplicate files are created and provides easy access to the newly generated or existing file through user prompts.

function createNewFile(){
  // Check if the file already exists
  var folder = DriveApp.getFolderById(FOLDERID);
  var existingFiles = checkExistingFiles();
  var existingURLs = checkExistingURLs();
  var name = getFlightCode();

  // If the file doesn't exist, create it and paste the info
  if (existingFiles.includes(name) == false){

    // Create the file and get it's ID
    var newSpreadsheet = SpreadsheetApp.create(name);
    var fileId = newSpreadsheet.getId();
    var file = DriveApp.getFileById(fileId);

    // Add new tabs and name them. Paste Titles from Master Sheet
    var ss = SpreadsheetApp.openById(fileId);

    var tabs = ['Guest Details', 'Dietaries', 'Emergency Details', 'Insurance Details', 'Room Allocations','Extras'];
    var masterRanges = ["A6:O7","Q6:S7",'U6:Z7','AB6:AG7','AI6:AK7','AM6:BB7'];
    var titleRanges = ["A1:O1","A1:C1",'A1:F1','A1:F1','A1:C1','A1:O1'];
    var subTitleRanges = ["A2:O2","A2:C2",'A2:F2','A2:F2','A2:C2','A2:O2'];

    for (var i = 0; i < tabs.length; i ++) {
      var tabName = tabs[i];
      var newSheet = ss.insertSheet();
      newSheet.setName(tabName);

      copyTitles(fileId,tabs[i],masterRanges[i]);
      formatTitles(fileId,tabs[i],titleRanges[i],subTitleRanges[i]);

      newSheet.autoResizeColumns(1,15);
    }

    var sheetToDelete = ss.getSheetByName("Sheet1");
    ss.deleteSheet(sheetToDelete);

    // Add Data
    addGuestInformation(name,fileId,'Guest Details');
    addDietaries(name,fileId,'Dietaries');
    addEmercencyDetails(name,fileId,'Emergency Details');
    addInsuranceDetails(name,fileId,'Insurance Details');
    addRoomAllocations(name,fileId,'Room Allocations');
    addExtras(name,fileId,'Extras');

    // Move file to desired folder and remove it from Root
    // folder.addFile(file);
    // DriveApp.getRootFolder().removeFile(file);
    var sheetURL = file.getUrl();

    // Show Success message and button to access the new report
    var message = 'The report was successfully created. File Name: ' + name + '\n \nPress "OK" to go to the file';
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert('Report Created', message, ui.ButtonSet.OK_CANCEL);
    if (response == ui.Button.OK) {
      var html = '<script> window.open("' + sheetURL + '"); google.script.host.close(); </script>';
      ui.showModalDialog(HtmlService.createHtmlOutput(html), 'Existing File');
    }

    return fileId;
  } else {
    var nameIndex = existingFiles.indexOf(name);
    var nameURL = existingURLs[nameIndex];
    var message = 'It appears that the Trip Code you are trying to use already has a file created. If you want to create it again, delete the previous file. \n \nPress "OK" to go to the file';
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert('File Already Exists', message, ui.ButtonSet.OK_CANCEL);
    if (response == ui.Button.OK) {
      var html = '<script> window.open("' + nameURL + '"); google.script.host.close(); </script>';
      ui.showModalDialog(HtmlService.createHtmlOutput(html), 'Existing File');
    }
}

This function automates the process of populating, formatting, and conditionally highlighting a Google Sheet tab with emergency insurance data from a master file. It pulls relevant data, formats it for readability, and hides unnecessary columns, ensuring that only the most relevant information is displayed.

Similar processes, with greater or lesser complexity, were created for each spreadsheet tab.

function addEmercencyInsuranceDetails(sheetName, sheetId, tabName){
  var sheet = SpreadsheetApp.openById(sheetId);
  var EmerInsurSheet = sheet.getSheetByName(tabName);
  var cell = EmerInsurSheet.getRange("A3");
  var likeAux = "'%" + sheetName + "%'"; 

  var formula = cell.setFormula(
    '=QUERY({IMPORTRANGE("' + MASTERFILEID + '","Master_Sheet_Temp!A:C"),IMPORTRANGE("' + MASTERFILEID + '","Master_Sheet_Temp!AJ:AM"),IMPORTRANGE("' + MASTERFILEID + '","Master_Sheet_Temp!V:Y"),ARRAYFORMULA(SWITCH(IMPORTRANGE("' + MASTERFILEID + '","Master_Sheet_Temp!T:T"),FALSE,"N",TRUE,"Y",IMPORTRANGE("' + MASTERFILEID + '","Master_Sheet_Temp!T:T")))},"SELECT Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 WHERE Col1 LIKE ' + likeAux + '",0)'
  )

  // Arrange some columns
  EmerInsurSheet.getRange("A:Z").setVerticalAlignment("middle").setWrap(true);
  EmerInsurSheet.setColumnWidth(1,200);
  EmerInsurSheet.setColumnWidth(2,300);
  EmerInsurSheet.setColumnWidth(6,300);
  EmerInsurSheet.setColumnWidth(10,600);

  // Set Conditional Format Rule
  var conditionalRange = EmerInsurSheet.getRange("A3:K");
  var rule = SpreadsheetApp.newConditionalFormatRule()
                           .whenFormulaSatisfied('=$K3="Y"')
                           .setBackground("#e8d4dc")
                           .setRanges([conditionalRange])
                           .build();
  var rules = EmerInsurSheet.getConditionalFormatRules();

  // Hide Canceled Column
  var hideTitle = EmerInsurSheet.getRange("K2");
  hideTitle.setValue("Canceled");
  var hideRange = EmerInsurSheet.getRange("K:K");
  EmerInsurSheet.hideColumn(hideRange);

  rules.push(rule);
  EmerInsurSheet.setConditionalFormatRules(rules);  
}