Google Sheets google apps script add a VLOOKUP function for all rows

I have a sheet with a number of VLOOKUPS which do work BUT whenever a new row is added the VLOOKUP formula is not added to the new row. I’m certain this can be done with app script but I don’t know exactly where to start.

It is important (OK vital) that the VLOOKUPs are on every row even new rows when added. I do have a duplicate row function that operates from a custom menu but I know that some users will use the default Google Add “X” rows button which does not replicate functions.

 function onOpen() {
   SpreadsheetApp.getUi() 
      .createMenu('custom menu')
      .addItem('Add rows','duplicateLastRow')
      .addSeparator()
      .addItem('Help','showSidebar')
      .addToUi();
}

function duplicateLastRow(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  ss.insertRowsAfter(ss.getLastRow(),10);
  ss.getRange(ss.getLastRow(),1,1,ss.getLastColumn()).copyTo(ss.getRange(ss.getLastRow()+1,1,10));
}

Here is one of the VLOOKUPS =if(A2 > 0,VLOOKUP(A2,Sheet2!A$2:G,3,false),"")

What I’m looking for is whenever a row in Col A is updated/added the VLOOKUP runs via a script as I understand that would ensure it applies to all rows, even new ones when added that way I don’t need the duplicate row function.

How do I structure the script function – thanks in advance

Answer

Google Sheet use ArrayFormula for only a cell to calculate the Vlookup for a range

Formula for you

=ArrayFormula(if(A2:A>0,VLOOKUP(A2:A,Sheet2!A$2:G,3,0),""))

Note: Make sure the other cells below the cell containing the ArrayFormula are cleared.

Advertisement

Answer