Google Script Help Script to pull Values or Formulas depending on Cell

Question

I have two functions that I am trying to combine into one or find a better way to write one function to get the proper end result.

My goal is to read a range of cells and append them to the first open set of columns to the right of the current data. Depending on what is in the cell, I would like it to either pull the value or the function of the cell. Some cells are text, some are numbers imported from other sheets and some are formulas that react to those numbers. While doing that, I also need it to pull the conditional formatting from the cells as well.

Here is what I currently have written and need to somehow combine recordValue and recordFormulas into one function that properly pulls the right information to the appended cells.

Any and all help would be appreciated!

 function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Record value')
      .addItem('Record Formulas','testFormulas')
      .addToUi();
}

function testFormulas() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");
    var row1 = 1;
    var column1 = 31;
    var row2 = 40;
    var column2 = 6;
    var values = sheet.getRange(row1,column1,row2,column2).getValues();
    var formulas = sheet.getRange(row1,column1,row2,column2).getFormulasR1C1();
    var i=0;
    var j=0;
    var rules = sheet.getConditionalFormatRules();
    var newRules = [];
    var sheet = ss.getSheets()[0];

  
  
  newRules = newRules.concat(rules);
    for( i=1; i<formulas.length; i++ ) {
      for( j=4; j<formulas[0].length; j++ ) {
        if( formulas[i][j] !== "" ) values[i][j] = formulas[i][j];
       
      }
    }
    Logger.log(values);
    sheet.getRange(1,1,values.length,values[0].length).setValues(values);
    sheet.getRange(1,1,values.length,values[0].length).setNumberFormats(format);
  }
  catch(err) {
    Logger.log(err);}

     var rowToWriteCounter = 1;
  const firstColumnAvailable = sheet.getLastColumn() + 1;

  var columnToWriteCounter;

  for (var row in values) {

    columnToWriteCounter = firstColumnAvailable;
    
    for (var col in values[row]) {

      //write to the new cell
      sheet.getRange(rowToWriteCounter, columnToWriteCounter).setValue(values[row][col]).setBorder(true, true, true, true, false, false);

      //this part checks for conditional formatting
      for (var r = 0; r < rules.length; r++) {
        var rule = rules[r];
        //Get condition for each rule
        var booleanCondition = rule.getBooleanCondition();
  
        //Get the ranges to which each rule applies and iterate through
        var ranges = rule.getRanges();
        for (var i = 4; i < ranges.length; i++) {
          var ruleColumn = ranges[i].getColumn();
          var ruleRow = ranges[i].getRow(); 
          
         
          //If condition isn't null and edited column is the same as the one in the range, add rule
          if ((ruleColumn == Number(col)+1) && (ruleRow == Number(row)+1) && (booleanCondition != null)) {
            var newRule = SpreadsheetApp.newConditionalFormatRule()
              .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
              .setBackground(booleanCondition.getBackgroundObject())
              .setBold(booleanCondition.getBold())
              .setItalic(booleanCondition.getItalic())
              .setRanges([sheet.getRange(rowToWriteCounter, columnToWriteCounter)])
              .build();
            newRules.push(newRule);
          }
        }
      }
      sheet.setConditionalFormatRules(newRules);
      columnToWriteCounter++;
    }
    rowToWriteCounter++;
  
  }

}

Answer 1

Here is a simple test case for combining values and formulas.

function test() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Sheet3");
    var values = sh.getDataRange().getValues();
    var formulas = sh.getDataRange().getFormulas();
    var format = sh.getDataRange().getNumberFormats();
    var i=0;
    var j=0;
    for( i=0; i<formulas.length; i++ ) {
      for( j=0; j<formulas[0].length; j++ ) {
        if( formulas[i][j] !== "" ) values[i][j] = formulas[i][j];
      }
    }
    Logger.log(values);
    sh = ss.getSheetByName("Sheet4");
    sh.getRange(1,1,values.length,values[0].length).setValues(values);
    sh.getRange(1,1,values.length,values[0].length).setNumberFormats(format);
  }
  catch(err) {
    Logger.log(err);
  }
}