Google Sheets – How to Use Google Script Replace Function

google sheetsgoogle-apps-script

Is it possible to build a Google Script where I can select a cell, take its function, replace the cell references (instead of C2,C3,C4, etc. make it AC1,AC2,AC3, etc.), and then return the output of the new function?

If so, how would one go about writing that script?

Best Answer

Like this:

Get the selected cell (for simplicity, I deal only with one cell even if a larger range is selected)
Get the formula from it.
If there is a formula, replace all references to C(number) with AC(number).
Put the new formula into the cell.

function replaceReferences() {
  var cell = SpreadsheetApp.getActiveSheet().getActiveRange().getCell(1,1);
  var formula = cell.getFormula();
  if (formula) {
    var newFormula = formula.replace(/(C\delta+)/g, "A$1");
    cell.setFormula(newFormula);
  }
}

Related Solutions

Google-sheets – More Efficient Filtering Script

The key approach is to minimize the API calls. Performing those in for loops will slow down the process considerably, especially with 900 rows. I've re-worked your code in such a way, that it calls for the Spreadsheet API only twice:

function open_interventions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // start logger time  
  var start = new Date();

  // collect data and sort
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn())
    .sort([
      {column: 1, ascending: true}, 
      {column: 3, ascending: false},
      {column: 2, ascending: true}])
    .getValues();

  // set counters
  var cFirst = 0, cSecond = 0;

  // itterate to find number of "Open" rows
  for(var i=0, dLen=data.length; i<dLen; i++) {
    if(data[i][0] == 'Open') {
      cFirst++;
    } 
  }

  // itterate through array (backwards) to calculate the rowIndex 
  for(var j=dLen=data.length-1; j>=0; j--) {
    if(data[j][0] != 'Open') {      
      cSecond++;
    } else {
      break;
    }
  }

  // calculate rowIndex
  var rIndex = data.length - (cFirst+cSecond);

  // hide all rows
  sheet.hideRows(2, data.length); 

  // show (unhide) only "Open" rows
  sheet.showRows(rIndex+2, cFirst); 

  sheet.setActiveSelection("D1")
    .setValue("Execution time (ms): " + (new Date() - start).toString());
}

I've added a time tracker thingy to it, so that you can monitor the execution speed. See example file I created: Efficient Sorting. When the file is opened, it will add another menu item called Sorting. There you can play around and see what happens.

Google-sheets – Batch Hiding Function in Google Script

To hide rows, Google Apps Script only include the methods that the OP already mentioned. The one that is able to hide several rows at a time, hideRows(rowIndex,rows) works for consecutive rows. If the rows are not consecutive, one alternative is to sort the data in advance in order to make the rows to hide to be consecutive.

Best Answer

Related Solutions

Google-sheets – More Efficient Filtering Script

Google-sheets – Batch Hiding Function in Google Script

Related Topic