Generate a summary of finances from several different google sheets

financeSummary.gs

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive()
  var menuItems = [
    { name: "Update Summaries...", functionName: "updateSummaries_" }
  ]
  spreadsheet.addMenu("Finance", menuItems)
}

/**
 * A function that updates the summary sheets
 */
function updateSummaries_() {
  var spreadsheet = SpreadsheetApp.getActive()
  var actualSheet = spreadsheet.getSheetByName("Summary Actual")
  var projectedSheet = spreadsheet.getSheetByName("Summary Projected")

  var ssArr = getBudgetSheets_()
  generateSummarySheet_(actualSheet, ssArr, "SummaryActual")
  generateSummarySheet_(projectedSheet, ssArr, "SummaryProjected")
}

/** 
 * Generates a single summary sheet
 */
function generateSummarySheet_(sheet, ssArr, rangeName) {
  sheet.clear()
  sheet.activate()
  var headers = ssArr[0].getRangeByName("SummaryHeaders").getValues()
  var rangeNumRows = 4
  var rangeNumColumns = 9
  var rowStep = 5
  var currentRow = 2
  sheet
    .getRange(1, 1, 1, rangeNumColumns)
    .setValues(headers)
    .setBackground("#cccccc")
    .setFontWeight("bold")
  ssArr.forEach(function(ss) {
    var range = ss.getRangeByName(rangeName)
    sheet.getRange(currentRow, 1).setValue(ss.getName())
    sheet
      .getRange(currentRow, 1, 1, rangeNumColumns)
      .merge()
      .setFontWeight("bold")
    sheet
      .getRange(currentRow + 1, 1, rangeNumRows, rangeNumColumns)
      .setValues(range.getValues())
    sheet
      .getRange(currentRow + 1, 2, rangeNumRows, rangeNumColumns - 1)
      .setNumberFormat("£#.00")
    currentRow += rowStep
  })
  sheet.setFrozenRows(1)
}

/**
 * Uses a sheet named "URLS" with the urls for each sheet to grab the individual budget sheets
 */
function getBudgetSheets_() {
  var spreadsheet = SpreadsheetApp.getActive()
  var urls = spreadsheet.getRangeByName("URLS")
  var urlArr = urls
    .getValues()
    .map(function(row) {
      return row[0]
    })
    .filter(function(el) {
      return Boolean(el)
    })

  var ssArr = []
  urlArr.forEach(function(sheetUrl) {
    try {
      ssArr.push(SpreadsheetApp.openByUrl(sheetUrl))
    } catch (err) {
      Logger.log(err)
      Browser.msgBox(
        "Error",
        "There was a problem with this url: " + sheetUrl,
        Browser.Buttons.OK
      )
    }
  })
  return ssArr
}