My scripts for Google Docs Apps Script.

google-docs.js

/**
 * Sums cell values in a range if they have the given background color
 * 
 * @param  {Array<number>} range Values of the desired range
 * @param  {String} colorRef     Label to color reference to match values on range
 * 
 * @return {number}              Sum of all cell values matching the condition
 * @customfunction
 */
function sumByColor(range, colorRef) {
  const activeRange = SpreadsheetApp.getActiveRange()
  const activeSheet = SpreadsheetApp.getActiveSheet()
  const activeFormula = activeRange.getFormula()

  const rangeAddress = activeFormula.match(/\((.*)\,/).pop().trim()
  const colorRefAddress = activeFormula.match(/\,(.*)\)/).pop().trim()
  const backgrounds = activeSheet.getRange(rangeAddress).getBackgrounds()
  const bgRef = activeSheet.getRange(colorRefAddress).getBackground()

  return backgrounds.reduce((acc, cur, i) => {
    return cur == bgRef ? acc + Number(range[i]) : acc
  }, 0)
}

/**
 * Take stock of the previous day's expenses and
 * if it's the first day of the month, make a monthly balance
 * schedule to run daily between 23 and 00
 *
 * @customfunction
 */
function balance() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet()
  const currentSheet = activeSheet.getSheetByName('Mês atual')
  const historySheet = activeSheet.getSheetByName('Histórico')
  const currentDate = new Date()
  const tomorrow = new Date(currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate() + 1)
  const historyData = historySheet.getRange('B3:E1000').getValues()
  const currOutgoings = currentSheet.getRange('F14:G34')
  const currIncomings = currentSheet.getRange('I6:J36')
  const filteredCurrOutgoings = currOutgoings.getValues().filter(e => e[0] != '').map(e => [...e, 'DESPESA'])
  const filteredCurrIncomings = currIncomings.getValues().filter(e => e[0] != '').map(e => [...e, 'ENTRADA'])
  const monthlyBalance = []
  const monthNames = ['Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho', 'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro']
  const formattedCurrentDate = Utilities.formatDate(currentDate, 'America/Sao_Paulo', 'dd/MM/yyyy')

  // Complete the monthly balance sheet
  for (let i = 0; i < historyData.length; i++) {
    const entryDate = new Date(historyData[i][0])
    if (entryDate.getFullYear() !== currentDate.getFullYear() || entryDate.getMonth() !== currentDate.getMonth()) {
      continue
    }
    const entry = {
      date: entryDate,
      description: '',
      value: 0,
      type: '',
      line: 3 + i
    }

    for (let j = 1; j < historyData[i].length; j++) {
      const cell = historyData[i][j]
      if (cell != '') {
        switch (j) {
          case 1:
            entry.description = cell
            break
          case 2:
            entry.value = Number(cell)
            break
          case 3:
            entry.type = cell
            break
        }
      }
    }
    monthlyBalance.push(entry)
  }

  const monthlyBalanceDescriptions = monthlyBalance.map(b => b.description.toString())
  const toAddHistory = filteredCurrOutgoings.concat(filteredCurrIncomings).filter(e => !monthlyBalanceDescriptions.includes(e[0].toString()))
  let historyLastLine = monthlyBalance[monthlyBalance.length - 1]?.line || 2

  Logger.log('Current montly history balance: ' + JSON.stringify(monthlyBalance))
  Logger.log('To add in history: ' + toAddHistory)

  for (let i = 0; i < toAddHistory.length; i++) {
    historyLastLine++
    const entry = {
      date: formattedCurrentDate,
      description: toAddHistory[i][0],
      value: toAddHistory[i][1],
      type: toAddHistory[i][2],
      line: historyLastLine
    }
    const color = toAddHistory[i][2] == 'ENTRADA' ? '#00CD00' : '#FF5252'

    const dateCell = historySheet.getRange('B' + entry.line)
    const descCell = historySheet.getRange('C' + entry.line)
    const valueCell = historySheet.getRange('D' + entry.line)
    const typeCell = historySheet.getRange('E' + entry.line)

    dateCell.setValue(entry.date)
    descCell.setValue(entry.description)
    valueCell.setValue(entry.value)
    typeCell.setValue(entry.type)

    dateCell.setBackground(color)
    descCell.setBackground(color)
    valueCell.setBackground(color)
    typeCell.setBackground(color)

    monthlyBalance.push(entry)
  }

  if (tomorrow.getDate() == 1) {
    Logger.log('Last day of the month. Carrying out a general balance')
    const color = '#FF5252'
    const entry = {
      date: formattedCurrentDate,
      description: 'Despesas recorrentes',
      value: Number(currentSheet.getRange('G35').getValue()),
      type: 'DESPESA',
      line: ++historyLastLine
    }
    monthlyBalance.push(entry)

    const dateCell = historySheet.getRange('B' + entry.line)
    const descCell = historySheet.getRange('C' + entry.line)
    const valueCell = historySheet.getRange('D' + entry.line)
    const typeCell = historySheet.getRange('E' + entry.line)

    dateCell.setValue(entry.date)
    descCell.setValue(entry.description)
    valueCell.setValue(entry.value)
    typeCell.setValue(entry.type)
    dateCell.setBackground(color)
    descCell.setBackground(color)
    valueCell.setBackground(color)
    typeCell.setBackground(color)

    const balanceInterval = currentSheet.getRange('B6:B14').getValues()
    for (var i = 0; i < balanceInterval.length; i++) {
      if (balanceInterval[i] == '') {
        const line = 6 + i
        const totalBalanceOfMonth = currentSheet.getRange('C18:C19').getValue()
        currentSheet.getRange('B' + line).setValue(monthNames[currentDate.getMonth()] + ' de ' + currentDate.getFullYear())
        currentSheet.getRange('C' + line).setValue(totalBalanceOfMonth)
        break
      }
    }

    Logger.log('Cleaning month data')
    currOutgoings.clearContent()
    currIncomings.clearContent()
  }

  const firstDayOfNextMonth = new Date(tomorrow.getFullYear(), tomorrow.getMonth() + 1, 1)
  const lastDayOfMonth = new Date(firstDayOfNextMonth - 1)
  const daysToLastDay = Math.ceil((lastDayOfMonth - tomorrow) / (1000 * 60 * 60 * 24)) - 1
  currentSheet.getRange('C20:C21').setValue(daysToLastDay)
  Logger.log('Update days to close month: ' + daysToLastDay)
}