Custom App Scripts to generate sheets and calendar events using data from a spreadsheet

GoogleSheetAppScripts.gs

var CALENDAR_ID = 'XXX';
var CRUNCH_TEMPLATE_ID = 'XXX';
var CRUNCH_FOLDER_ID = 'XXX';
var NA = 'N/A';
var INVALID_DATE = 'Invalid Date';
var CALENDAR = CalendarApp.getCalendarById(CALENDAR_ID);

/* Populate custom menu */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'VIEWINGS - Update calendar', functionName: 'createEvents'},
    {name: 'CRUNCH - Create crunch sheet for selected viewing', functionName: 'createCrunchSheet'},
    // {name: 'VIEWINGS - Reset calendar', functionName: 'resetCalendar'},
    // {name: 'Get details from selection', functionName: 'getDeetsFromSelection'}
  ];
  spreadsheet.addMenu('Nest', menuItems);
}

// Extract data from the selected row
function getSortedRowData(rowData) {
  return {
    agency: {
      name: rowData[0] || NA,
      agent: rowData[1] || NA,
      email: rowData[2] || NA,
      phone: rowData[3] || NA
    },
    comparable: {
      address: rowData[4] || NA,
      link: rowData[5] || NA,
      size: rowData[6] || NA,
      price: rowData[16] || NA,
      date: rowData[7] || NA
    },
    viewing: {
      date: rowData[8] || NA,
      time: rowData[9] || NA
    },
    property: {
      type: rowData[10] || NA,
      address: rowData[11] || NA,
      link: rowData[12] || NA,
      size: rowData[13] || NA,
      price: rowData[17] || NA
    },
    notes: rowData[24] || NA
  };
}

/* Return the data from the active sheet */
function getSheetData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  return sheet.getDataRange().getValues();
}

/* Return the data from the selection */
// Currently this only works if the entire row is selected
function getDataFromSelection() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var range = selection.getActiveRange();
  return range.getValues();
}

/* Turns single digit integer to 2 digits */
function get2DigitNumber(number) {
  return number < 10 ? '0' + number : number;
}

/* Format price - decorate with commas for better readability */
function getFormattedPrice(price) {  
  var splitNumber = price.toString().split('.');
  integer = splitNumber[0];
  var formattedPrice = [];
  
  for (var i = integer.length - 1; i >= 0; i --) {
    formattedPrice.splice(0, 0, integer[i]);
    
    if ((integer.length - i) % 3 === 0 && i > 0) {
      formattedPrice.splice(0, 0, ',');
    }
  }

  formattedPrice.splice(0, 0, '£');
  if (splitNumber[1]) {
    formattedPrice.push('.', splitNumber[1]);
  }

  return formattedPrice.join('');
}




//////////////////////////////////////////////////////
// CRUNCH SHEET

// Create new crunch sheet from selection
function createCrunchSheet() {
  // To be safe only create one file at per selection
  var selectedData = getDataFromSelection();
  var data = getSortedRowData(selectedData[0]);
  
  var newFile = createNewCrunchSheetFromData(data, addCrunchLinkToSelectedRow);
}

function createNewCrunchSheetFromData(data, callback) {
  if (!data) {
    return;
  }
  
  var date = new Date(data.viewing.date);  
  if (data.property.address && date !== INVALID_DATE) {
    var fileId = CRUNCH_TEMPLATE_ID;
    var formattedDate = date.getFullYear() + '-' + get2DigitNumber(date.getMonth() + 1) + '-' + get2DigitNumber(date.getDate() + 1);
    var fileName = data.property.address + ' (' + formattedDate + ')';
    var newFile = DriveApp.getFileById(fileId).makeCopy(fileName, DriveApp.getFolderById(CRUNCH_FOLDER_ID));
    
    var sheet = SpreadsheetApp.open(newFile);
    sheet.getRange('B2').setValue(data.property.address);
    sheet.getRange('B3').setValue(data.property.link);
    sheet.getRange('B4').setValue(data.property.size);
    sheet.getRange('B5').setValue(data.property.price);
    sheet.getRange('E2').setValue(data.comparable.address);
    sheet.getRange('E3').setValue(data.comparable.link);
    sheet.getRange('E4').setValue(data.comparable.size);
    sheet.getRange('E5').setValue(data.comparable.price);
    sheet.getRange('E6').setValue(data.comparable.date);
    sheet.getRange('H2').setValue(data.agency.name);
    sheet.getRange('H6').setValue(data.notes);
    
    if (callback) {
      callback(newFile.getUrl());
    }
  }
}

function addCrunchLinkToSelectedRow(url) {
  Logger.log(url);
  var DOM = '<div style="font-family: Helvetica, Arial, sans-serif; font-size: 12px;">';
  DOM += '<h2>Crunch URL:</h2>';
  DOM += '<a href="' + url + '" target="_blank">Open new Crunch</a>'
  DOM += '</div>';
  
  var html = HtmlService.createHtmlOutput(DOM)
      .setTitle('Viewing Deets')
      .setWidth(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}




//////////////////////////////////////////////////////
// CALENDAR

/* Wipe all events from the calendar */
function resetCalendar() {
  var events = CALENDAR.getEvents(new Date('1/1/2018'), new Date('1/1/2020'));

  for (var i = 0; i < events.length; i ++) {
    var event = events[i];
    event.deleteEvent();
  }
}

/* Create events in the calendar */
function createEvents() {
  // Wipe events to avoid dupes
  resetCalendar();
  
  var dataRows = getSheetData();
  for (var i = 2; i < dataRows.length; i++) {
    createCalendarEntry(dataRows[i]);
  }
}

// Create new calendar events from selection
function createCalendarEntry(rowData) {
  var data = getSortedRowData(rowData);

  // Skips the entry if the time isn't set
  if (data.viewing.time !== NA && typeof data.viewing.time !== 'object') {
    // Fetches the event time
    var eventTitle = data.property.type + ' (' + data.property.size + 'm²) - ' + getFormattedPrice(data.property.price);
    var eventStartTime = new Date(data.viewing.date);
    var splitTime = data.viewing.time.split(":");
    eventStartTime.setHours(splitTime[0]);
    eventStartTime.setMinutes(splitTime[1]);

    //Only creates the event if the start date is valid
    if (eventStartTime !== INVALID_DATE) {
      var eventEndTime = new Date(eventStartTime.getTime() + 30 * 60000);

      // Gather all the information for the description
      var description =   '<b>Link:</b> ' + '<a href="' + data.property.link + '" style="background-color: blue !important;">' + data.property.link + '</a>';
          description +=  '\n<b>Agency:</b> ' + data.agency.name;
          description +=  '\n<b>Agent:</b> ' + data.agency.agent + ' - ' + '<a href="tel:' + data.agency.phone + '">' + data.agency.phone + '</a>';
      
      CALENDAR.createEvent(eventTitle,
        eventStartTime,
        eventEndTime, {
          location: data.property.address,
          description: description
      });
    }
  }
}


/*function getHTMLFromData(data, firstRow) {
  var html = '<div style="font-family: Helvetica, Arial, sans-serif; font-size: 12px;"><ul style="list-style: none; padding: 0;">';

  for (var i = firstRow; i < data.length; i++) {
    var entryHTML = '<li>'
    entryHTML += '<h2>' + data[i][5] + ' (AP: £' + data[i][16] + ')' + '</h2>'
        entryHTML += '<ul style="list-style: none; padding: 0;">'
        entryHTML += '<li><span style="font-weight: 700;">Link:</span> <a href="' + data[i][6] + '">' + data[i][6] + '</a></li>'
          entryHTML += '<li><span style="font-weight: 700;">Type:</span> ' + data[i][4] + '</li>'
          entryHTML += '<li><span style="font-weight: 700;">Price:</span> £' + data[i][16] + '</li>'
          entryHTML += '<li><span style="font-weight: 700;">Agency:</span> ' + data[i][0] + '</li>'
          entryHTML += '<li><span style="font-weight: 700;">Agent:</span> ' + (data[i][1] || 'N/A') + '</li>'
          entryHTML += '<li><span style="font-weight: 700;">Agent tel:</span> <a href="' + data[i][3] + '">' + data[i][3] + '</a></li>'
        entryHTML += '</ul>'
        entryHTML += '<br><hr>'
    entryHTML += '</li>'
    
    html += entryHTML
  }
  
  html += '</ul></div>'
  
  return html
}*/

/*function getDeetsOfAll() {  
  showDataOnSideBar(getData(), 1);
}*/

/*function getDeetsFromSelection() {
  showDataOnSideBar(getDataFromSelection(), 0);
}*/

/*function showDataOnSideBar(data, startIndex) {
  var html = HtmlService.createHtmlOutput(getHTMLFromData(data, startIndex))
      .setTitle('Viewing Deets')
      .setWidth(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}*/