macro para atualizacao mensal de planilha de calculos

novo_mes.gs

var planilhas = {
    "meu": {
        calcCol: "F",
    },
    "kassia": {
        calcCol: "G",
    },
  "casal": {
        calcCol: "H",
    },
    "moto": {
        calcCol: "I",
    },
    "pessoas": {
        calcCol: "J",
    }
    
};
var linhaDeCalculoDoUltimoMes = undefined;
var numeroDoMes = undefined;

function main() {
    //numeroDoMes = getNumeroDoMes();
    linhaDeCalculoDoUltimoMes = getLinhaDoUltimoMes();
    for (var i in planilhas) {
        upPlanilha(i);
    }
}

function upPlanilha(planilha) {
    var linha = ultimaLinhaDaPlanilha(planilha);
    var colunaDeCalculoDaPlanilha = planilhas[planilha].calcCol;
    upFormulas(planilha, linha, colunaDeCalculoDaPlanilha);
  var CREDITO_COL = "C";
  var DEBITO_COL = "D";
  var DINHEIRO_COL = "E";
  upFormulasPagamento(planilha, linha, CREDITO_COL);
  upFormulasPagamento(planilha, linha, DEBITO_COL);
  upFormulasPagamento(planilha, linha, DINHEIRO_COL);
}

function upGastoTotal() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B7').activate();
    spreadsheet.getRange('B6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

function ultimaLinhaDaPlanilha(planilha) {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(planilha), true);
    var range = spreadsheet.getRange('A:B').activate();
    var values = range.getValues();

    for (var i = 0; i < values.length; i++) {
        var COLUNA_ITEM = 1;
        var COLUNA_DATA = 0;
        var data = undefined;
        var mesIndex = undefined;
        for (var i = 0; i < values.length; i++) {
            if (values[i][COLUNA_ITEM]) {
                if (values[i][COLUNA_DATA]) {
                    data = values[i][COLUNA_DATA];
                }
                else {
                    values[i][COLUNA_DATA] = data;
                    var dateStr = formatDate(data);
                    range.getCell(i + 1, 1).setValue(dateStr);
                }

            }
            else {
                return i;
            }

        }

    }

}

function getNumeroDoMes() {
    var ui = SpreadsheetApp.getUi();
    var response = ui.prompt('Escrever o número do mês');
    var numeroDoMes = response.getResponseText();
    return numeroDoMes;
}
function formatDate(date) {
    var dayDict = {
        0: "dom.",
        1: "seg.",
        2: "ter.",
        3: "qua.",
        4: "qui.",
        5: "sex.",
        6: "sab.",
    }
    var day = date.getDay();
    var dayStr = dayDict[day];

    var dia = date.getDate();
    var month = date.getMonth() + 1;
    var year = date.getFullYear().toString().slice(2, 4);
    var dateStr = dayStr + " " + dia + "/" + month + "/" + year;
    return dateStr;
};

function getLinhaDoUltimoMes() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('calculos'), true);
    var range = spreadsheet.getRange('A:A').activate();
    var values = range.getValues();

    for (var i = 1; i < values.length; i++) {
        var COLUNA_MES = 0;
        if (!values[i][COLUNA_MES])
            return i;
    }
}

function upFormulas(planilha,linhaPlanilha, colunaCalculo) {
  
    var linhaCalculo = linhaDeCalculoDoUltimoMes;
    var formulaCurr = upFormulasUltimoMes(planilha,linhaPlanilha, colunaCalculo + "" + linhaCalculo);
    upFormulasNovoMes(planilha, linhaPlanilha + 1, colunaCalculo + "" + (linhaCalculo + 1), formulaCurr);
}

function upFormulasUltimoMes(planilha, linhaPlanilha, celulaCalculo) {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('calculos'), true);
    spreadsheet.getRange(celulaCalculo).activate();
    var formulaCurr = spreadsheet.getCurrentCell().getFormula();
    var regexpStr = "("+planilha + "!" + "[A-z][0-9]+:[A-z])([);])";
    var regexp = new RegExp(regexpStr, "gi");
    var formulaUp = formulaCurr.replace(regexp, "$1" + linhaPlanilha + "$2");
    spreadsheet.getCurrentCell().setFormula(formulaUp);

    return formulaCurr;
}

function upFormulasNovoMes(planilha, linhaPlanilha, celulaCalculo, formulaCurr) {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('calculos'), true);
    spreadsheet.getRange(celulaCalculo).activate();
    var regexpStr = "("+planilha + "!" + "[A-z])[0-9]+:";
    var regexp = new RegExp(regexpStr, "gi");
    var formulaUp = formulaCurr.replace(regexp, "$1" + linhaPlanilha + ":");
    formulaUp = formulaUp.replace(/:([A-z])[0-9]+([);])/g, ":$1$2");
    spreadsheet.getCurrentCell().setFormula(formulaUp);
}


function upFormulasPagamento(planilha,linhaPlanilha, colunaCalculo){
    var linhaCalculo = linhaDeCalculoDoUltimoMes;
    var formulaCurr = upFormulasUltimoMes(planilha,linhaPlanilha, colunaCalculo + "" + linhaCalculo);
    upFormulasNovoMesPagamento(planilha, linhaPlanilha + 1, colunaCalculo + "" + (linhaCalculo + 1), formulaCurr);
}

function upFormulasNovoMesPagamento(planilha, linhaPlanilha, celulaCalculo, formulaCurr) {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('calculos'), true);
    spreadsheet.getRange(celulaCalculo).activate();
    var cell = spreadsheet.getCurrentCell();
    if(!cell.isBlank()){
      formulaCurr = cell.getFormula();
    }
    var regexpStr = "("+planilha + "!" + "[A-z])[0-9]+:";
    var regexp = new RegExp(regexpStr, "gi");
    var formulaUp = formulaCurr.replace(regexp, "$1" + linhaPlanilha + ":");
    formulaUp = formulaUp.replace(/:([A-z])[0-9]+([);])/g, ":$1$2");
    spreadsheet.getCurrentCell().setFormula(formulaUp);
}