How to copy or get google sheet cell reference as string easily?

Question

I hava a cell which is b30 in sheet sheet-alpha, I want get reference as string into clipboard, literally I want my clipboard contains string 'sheet-alpha'!B30

How do I do that in google sheet?

Answer 1

One solution to your need can be achieved in 2 steps:

First, open [top menu] Extensions > Apps Script and copy&paste the following function:

function getSheetName() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return sheetName;
}

The above code enables you to call the sheet name from wherever you need it by using the formula =getSheetName() in any cell.

Then, add another column to the right of column B (new Column C) and copy&paste the following array formula at the top most cell:

={"Ref 2 Str"; ARRAYFORMULA(IF(B2:B="","","'"&getSheetName()&"'!B:"&INDEX(ROW(INDIRECT("A"&(ROW()+1)&":A")))))}

The above function looks for non-empty cells on column B and for each non-empty cell it puts in column C the string reference for the corresponding cell in column B (in the format you need).