GetRange of a value (the value indicates a cell address)

November 5, 2021

I have in ‘Back’!AC3 a formula that returns a cell address (the formula is "A"&V2&":T"&V2 that returns ‘A44:T44’ (V2 can change)

I want to use this address in a macro :

function border() {
var spreadsheet = SpreadsheetApp.getActive();
  const doc = SpreadsheetApp.getActive();
  const sheet = doc.getSheetByName('Facture');
  const back = doc.getSheetByName('Back');
  var address = back.getRange('AC3').getValue();
  spreadsheet.sheet.getRange('address').activate();
  spreadsheet.getActiveRangeList().setBorder(null, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)

It doesn’t work… Can someone help me to fix it ?

Thank you !

>Solution :

Try building the range reference text string using concatenation, like this:

function border() {
  const ss = SpreadsheetApp.getActive();
  const rangeA1 = 'Facture!' + ss.getRange('Back!AC3').getValue();
  ss.getRange(rangeA1)
    .setBorder(false, true, true, true, false, false, '#000000', SpreadsheetApp.BorderStyle.SOLID);
}