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
CodePudding user response:
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.