Is there a way to reference a cell in Google Apps Scripts so that updates if a column or row is adde-CodePudding

I have a scripts in my sheet which which I use to clear a cell & then add a formula to force it to update. In the script I reference the specific cell to clear and then add the formula to.

The problem I am having is that if I add a column or row to the sheet which impacts the position of the cells referenced in the script, the script then still uses the old cell range and so performs the action on the wrong cell

e.g. the cell referenced in the script that I want to clear & add the formula to is AD10. If I insert a column this cell would then need to beceome AE10 but the script still works on cell AD10.

The script I am using is

function UpdateDraft() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AD10').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getCurrentCell().setFormula('=if($C$5<>"",IMPORTJSONAPI("https://api.sleeper.app/v1/draft/"&$C$5&"/picks" , "$..metadata.player_id", "@",1),)');
};

Thanks

Jon

CodePudding user response：

One easy way to do that is to create two named ranges: formulaCell in AD10, and parameterCell in C5.

The function then becomes:

function UpdateDraft() {
  const cell = SpreadsheetApp.getActive().getRange('formulaCell');
  const formula = '=if( len(parameterCell), IMPORTJSONAPI("https://api.sleeper.app/v1/draft/" & parameterCell & "/picks" , "$..metadata.player_id", "@",1), iferror(1/0) )';
  cell.clearContent().setFormula(formula);
}