Google Sheets – How to Apply Formula to Entire Column and Auto-Add to New Rows

google sheets

I need some kind of "autoincrement field" in my spreadsheet. I've added =ROW() formula to first column. But as I'm adding new rows – I have to apply this formula to new created rows as well. Is there any ways to do it automatically?

Best Answer

Use the following formula for that:

=ARRAYFORMULA(ROW(A:A))

I strongly advise you to use hard coded Id's. If for some reason the sheet gets re-arranged, then the Id's will follow with that re-arrangement. An ARRAYFORMULA will stick to its plan and present the range as ever.

Related Solutions

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total

Google Sheets – Apply Complicated Formula to Entire Column

I was able to reduce the formula and add ARRAYFORMULAE to make it work with ranges.

Formula

 =ARRAYFORMULA(
    IF(ISBLANK(A:A), 
      "", 
      SUBSTITUTE(
        ARRAYFORMULA(
          A:A & 
          IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & 
          "utm_campaign=" & 
          B:B & 
          "&utm_medium=" & 
          C:C & 
          "&utm_source=" & 
          D:D & 
          IF(E:E<>"","&utm_content=","") & 
          E:E & 
          IF(F:F<>"","&utm_term=","") & 
          F:F
        )
      , " ",  "%20"
      )
    )
  )

 copy / paste
 =ARRAYFORMULA(IF(ISBLANK(A:A), "", SUBSTITUTE(ARRAYFORMULA(A:A & IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & "utm_campaign=" & B:B & "&utm_medium=" & C:C & "&utm_source=" & D:D & IF(E:E<>"","&utm_content=","") & E:E & IF(F:F<>"","&utm_term=","") & F:F), " ",  "%20")))

Explained

It is not possible to use CONCATENATE formulae in combination with ARRAYFORMULAE. Therefore we need to go about this differently, by using an AMPERSAND to join different strings together. If the concatenated string is prepared and the ranges are chosen, then substitute all empty spaces with %20. This, together with an IF statement to check column A for presence, is all wrapped inside yet another ARRAYFORMULA.

Screenshot

enter image description here

Example

I've created an example file for you: Apply complicated formula to whole column