Replace the numerical part of cell reference with another number in google sheets

Question

I have a script that collects and updates data from a number of individual sheets and also a central master sheet. An individual sheet might contain 30 rows, whereas a master sheet will contain all the rows in all the sheets.

An individual sheet might contain a formula, say =A2+B2. On the individual sheet this formula is on row 2 but on the master sheet the formula might be in row 400. So that the formula works and yields correct data in the master sheet, the formula needs to be changed to =A400+B400.

I've tried to make this work by looking for cell references in a string that are delimited by an alphabetical character at the start, followed by one or more numbers and a space or a symbol at the end, iterating through and replacing every series of numerical characters that matches that condition with the new number value. With limited success.

Below is my attempts at this so far.

function isNumeric(n) 
{
  return !isNaN(parseFloat(n)) && isFinite(n);
}

function isAlpha(character)
{
  if ((character.charCodeAt(0) >= 65 && character.charCodeAt(0) <= 90) ||(character.charCodeAt(0)>=97 && character.charCodeAt(0)<=122))
  {
    return true;
  }
  else
  {
    return false;
  }
}

function correctFormula(formulaValue, destinationRow)
{
  //parse through the string and identify cell references eg A2, AI77. (One or more consecutive letters followed by one or more consecutive numbers.
  //Letters will never change but numbers will corresspond to the row in the array.
  //ignore any text enclosed by ""
  //ignore any text that does not start with =

  var strFormulaValue = String(formulaValue);
  if(strFormulaValue.charAt(0) === "=")
  {
    var i = 1;
    var inQuotes = false;
    var cellRef="";
    var originalLength = strFormulaValue.length;
    while (i<originalLength)
    {
      if (strFormulaValue.charAt(i)==="\"" || strFormulaValue.charAt(i)==="\'")
      {
        if (inQuotes)
        {
          inQuotes = false;
        }
        else
        {
          inQuotes = true;
        }
      }
      if (strFormulaValue.charAt(i)==="!")
      {
        //this is a cell ref to another sheet. Get to the end of this ref
        while((isAlpha(strFormulaValue.charAt(i)))||isNumeric(strFormulaValue.charAt(i)))
        {
          i++;
        }
      }

      if (isNumeric(strFormulaValue.charAt(i)) &&(!inQuotes))
      {
        if(isAlpha(strFormulaValue.charAt(i - 1)))
        {
          //this looks like it could be the start of a cell ref
          var numStart = i;
          var numEnd = -1;
          //find the last numerical digit in this sequence
          while (isNumeric(strFormulaValue.charAt(i)))
          {
            if (isNumeric(strFormulaValue.charAt(i)))
            {
              numEnd = i;
            }
            i++
          }
          if ((!isAlpha(strFormulaValue.charAt(i)))||(i>=strFormulaValue.length))//if the sequence of numeric digits ends as a  or the number digit was the last char in the string
          {
            //replace this string of numeric digits with the desired digits
            var formulaStart = strFormulaValue.substring(0, numStart);
            var formulaEnd = strFormulaValue.substring(numEnd + 1, strFormulaValue.length);
            strFormulaValue = formulaStart+ String(destinationRow)+formulaEnd;
          }

        }
      }

      i++;
    }
  return strFormulaValue;
  }
}

function testStrToFormula(stringval)
{
  Logger.log ("Inputting =A12, should convert to A1000");
  Logger.log (correctFormula("=A12", 1000));

  Logger.log ("Inputting =A12 + B12, should convert to A1000 + B1000");
  Logger.log (correctFormula("=A12 + B12 ", 1000));

  Logger.log ("Inputting =sum(A12:D12), should convert to =sum(A1000:D1000)");
  Logger.log (correctFormula("=sum(A12:D12)", 1000));

  Logger.log("Inputting = A12 & \"D3\"");
  Logger.log(correctFormula("=A12 & \"D3\""));

  Logger.log("Inputting =Sheet1!A1 * B1")
  Logger.log (correctFormula("=Sheet1!A1 * B1"))

}

This logs

[18-03-01 17:36:34:853 GMT] Inputting =A12, should convert to A1000
[18-03-01 17:36:34:854 GMT] =A1000
[18-03-01 17:36:34:854 GMT] Inputting =A12 + B12, should convert to A1000 + B1000
[18-03-01 17:36:34:855 GMT] =A1000 + B1000 
[18-03-01 17:36:34:856 GMT] Inputting =sum(A12:D12), should convert to =sum(A1000:D1000)
[18-03-01 17:36:34:858 GMT] =sum(A1000:D1000)
[18-03-01 17:36:34:859 GMT] Inputting = A12 & "D3"
[18-03-01 17:36:34:860 GMT] =Aundefined & "D3"
[18-03-01 17:36:34:861 GMT] Inputting =Sheet1!A1 * B1
[18-03-01 17:36:34:862 GMT] =Sheetundefined!A1 * B1

Not sure why I'm getting undefined in the middle of the string when I'm dealing with values in quotes or references to cells in other sheets. (References to cells on other sheets should not be altered)

RegEx might be the way to go: https://stackoverflow.com/questions/1897906/is-this-the-regex-for-matching-any-cell-reference-in-an-excel-formula — S RICHARDS, Mar 01 '18 at 15:03
Modified the question as I have partially solved the original question — S RICHARDS, Mar 01 '18 at 18:05

score 0 · Answer 1 · answered Mar 01 '18 at 16:29

0

I think the best solution is to let google sheets do the converting for you. You can use rng.copyTo(destinationRange) to copy the formula and values to your master sheet. This is will preserve the values and update the formula reference as well. (Equivalent to copying and pasting a row manually)

Here is an example code:

function copyFormula() {
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName("TargetSheet")
  var targetRng = sheet.getRange(1,1,1,3)
  Logger.log(targetRng.getValues())
  var desSheet = ss.getSheetByName("DestinationSheet")
  var desRng = desSheet.getRange(desSheet.getLastRow()+1,1,1,3)
  targetRng.copyTo(desRng)
}

This code will copy values and formula from row 1 (columns 1,2,3) of TargetSheet to the first available row of DestinationSheet. It will preserve the hard-coded values and auto update the formulas as well.

answered Mar 01 '18 at 16:29

Jack Brown

5,802
2
12
27

That is so incredibly useful. I didnt know that existed. I've been converting all values to formulas and using setFormulas() to transfer data. At this point, using this would mean a complete re-write of my program, which is going to need to happen at some point in the future, so pointing this out to me is more helpful than you realise but in terms of tomorrow and next week I still need to get this function working. – S RICHARDS Mar 01 '18 at 16:37
1

I would argue that the refactor is ultimately going to be much simpler and faster than attempting to fix your original code. – tehhowch Mar 01 '18 at 17:06
Sometimes simpler is better. I know rewriting your whole code is gonna take work, but so is trying to fix the current code. Either way, all the best. – Jack Brown Mar 01 '18 at 17:17
Not sure I can use this copyTo actually. 'Slave sheets' have to update the master sheet if a cell in the master sheet is blank but has a value in a slave sheet. If a cell value in the master sheet is changed, and there is a value in the slave sheet, it has to over-write the slave sheet value no matter what. I think this means the only way I can do it is copy everything to arrays and compare and update values, then write array contents back with setFormulas(). Thanks again all the same everybody :) Also, really great to know about this function now, it will come in useful in future work! – S RICHARDS Mar 01 '18 at 17:44

score 0 · Accepted Answer · answered Mar 08 '18 at 14:17

This code works, the function correctFormula(formula, destinationrow) will update all cell references in a formula to match the destination row excluding absolute references and external refs.

function isNumeric(n) 
{
  return !isNaN(parseFloat(n)) && isFinite(n);
}

function isAlpha(character)
{
  if ((character.charCodeAt(0) >= 65 && character.charCodeAt(0) <= 90) ||(character.charCodeAt(0)>=97 && character.charCodeAt(0)<=122))
  {
    return true;
  }
  else
  {
    return false;
  }
}

function isFormulaDelimeter(character)
{
  //=, +, -, /, *, :, %, [space], (,),", ',^,<,>,&,:
  switch(character)
  {
    case "=":
      return true;
      break;
    case "+":
      return true;
      break;
    case "-":
      return true;
      break;
    case "/":
      return true;
      break;
    case "*":
      return true;
      break;
    case ":":
      return true;
      break;
    case "%":
      return true;
      break;
    case " ":
      return true;
      break;
    case "(":
      return true;
      break;
    case ")":
      return true;
      break;
      //=, +, -, /, *, :, %, [space], (,),", ',^,<,>,&,:
    case "\"":
      return true;
      break;
    case "\'":
      return true;
      break;
    case "^":
      return true;
      break;
    case "<":
      return true;
      break;
    case ">":
      return true;
      break;
    case "&":
      return true
      break;
    case ":":
      return true
      break;      
    default:
      return false;
  }
}

function getTokens(formula) {
  var strFormula = String(formula);
  var index = 0;
  var tokens = [];
  while (index < strFormula.length)
  {
    var token = "";
    while ((!isFormulaDelimeter(strFormula.charAt(index))) && (index < strFormula.length))
    {
      token = token + strFormula.charAt(index)
      index ++;
    }
    tokens.push(token)
    if (isFormulaDelimeter(strFormula.charAt(index)))
    {
      tokens.push(strFormula.charAt(index))
      index++;
    }

  }
  return(tokens);
}

function correctFormula(formulaValue, destinationRow)
{
  //Logger.log(getTokens(formulaValue));
  var tokens = getTokens(formulaValue);
  var inQuotes = false;
  for (var index = 0; index < tokens.length; index ++)
  {


    if ((String(tokens[index]).indexOf("\"")) !== -1 || (String(tokens[index]).indexOf("\'") !== -1) && (!inQuotes))
    {
      inQuotes = true;
    }
    else if(String(tokens[index]).indexOf("\"") !== -1 || (String(tokens[index]).indexOf("\'") !== -1) && (inQuotes))
    {
      inQuotes = false;
    }
    //if it's in quotes, dont touch it
    if (!inQuotes)
    {
      //is it a cell reference?
      //if it's an external cell reference dont touch it (contains !)
      //if the number is preceded by a $ symbol, dont touch it
      //TODO - absolute cell erf where letter part is absolute but not the number part.
      var token = String(tokens[index]);
      for (var n=0; n<token.length; n++)
      {
        //the cell references we are interested in are purely characters followed by numbers

        if (isAlpha(token.charAt(0))||((String(token.charAt(0))==="$") && (isAlpha(token.charAt(1)))))//if its a cell ref or the first part is absolute
        {
          var itemRef = 1;
          while(isAlpha(token.charAt(itemRef)))
          {
            itemRef++;
          }
          if (isNumeric(token.charAt(itemRef)))
          {
            var numStart = itemRef;
            while(isNumeric(token.charAt(itemRef)))
            {
              itemRef ++;
            }
            if (itemRef == token.length)//if we are at the end and it was characters followed by numbers
            {
              var charPart = token.substring(0,numStart);
              token = charPart + String(destinationRow);
              tokens[index] = token;
            }

          }

        }
      }

    }


  }
  //put it all back together and return the result
  var formula = "";
  for (n=0; n< tokens.length; n++)
  {
    formula = formula + String(tokens[n]);
  }
  return formula;
}

You can use fall-through behavior for switch cases to reduce the repetition in the formula delimiter function. Alternately, consider storing the delimiters in an array, and using `Array.indexOf` to check if the character is there or not. — tehhowch, Mar 08 '18 at 17:02

Replace the numerical part of cell reference with another number in google sheets

2 Answers2