Converting Range ID to Range Object on Google Spreadsheet using Google Apps Script

submit.md

Converting Range ID to Range Object on Google Spreadsheet using Google Apps Script

This is a workaround for converting the range ID to the range object on Google Spreadsheet using Google Apps Script.

When the named range is put to a cell as the hyperlink as follows,

the hyperlink is like #rangeid=123456789. When this link is clicked, it moves to the cells of the named range. So it is considered that this value of #rangeid=123456789 includes the information about the range of the named range. But, unfortunately, in the current stage, there is no methods for directly retrieving the range object from the range ID. In this sample sample script, as a workaround, the range ID is converted to the range object using Google Apps Script.

The flow of this workaround is as follows.

1. Retrieve the range ID from a cell and create an URL with the range ID.
2. Open a dialog and access to the URL with the range ID.
   • By this, the Spreadsheet is opened and the range of the range ID is activated.
3. When the Spreadsheet is opened, the information of the active range is stored to the CacheService.
4. After several seconds, at Google Apps Script side, retrieve the data from CacheService.
5. Convert the data from CacheService to the range object.

By this flow, the range ID can be converted to the range object. I think that this might be a bit forcible. So when I could find other simple method, I would like to report it.

Usage:

When you want to test this workaround, please do the following flow.

1. Create Spreadsheet.

Create new Spreadsheet and set a named range, and please insert the hyperlink of the named range to a cell. In this sample, it's "A1".

2. Sample script.

Please copy and paste the following script to the script editor of the Spreadsheet.

Code.gs

function onOpen(e) {
  const range = e.source.getActiveRange();
  CacheService.getScriptCache().put(
    "range",
    JSON.stringify({
      sheetName: range.getSheet().getSheetName(),
      a1Notation: range.getA1Notation(),
    })
  );
}

// Please run this script.
function main(e) {
  const r = "A1"; // As a sample, it supposes that the hyperlink with the range ID is put in a cell "A1".

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!e) {
    const html = HtmlService.createTemplateFromFile("index");
    html.url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/edit${ss
      .getActiveSheet()
      .getRange(r)
      .getRichTextValue()
      .getLinkUrl()}`;
    SpreadsheetApp.getUi().showDialog(html.evaluate());
    return;
  }
  Utilities.sleep(15000); // When this value is small, no range data cannot be retrieved. Please be careful this.
  const text = CacheService.getScriptCache().get("range");
  if (!text) throw new Error("Range couldn't be retrieved.");
  const { sheetName, a1Notation } = JSON.parse(text);
  const range = ss.getRange(`'${sheetName}'!${a1Notation}`);

  // You can use this "range" as the range object retrieved from the range ID.
  const values = range.getValues();
  console.log(values); // You can see the values from the range object converted from the range iD.

  //
  // do something
  //
}

index.html

Please copy and paste the following script as the HTML file (index.html).

<iframe src="<?= url ?>"></iframe>
<script>
  google.script.run.withSuccessHandler(google.script.host.close).main(true);
</script>

3. Testing.

Please run main() function at the script editor. By this, a dialog is opened to the Spreadsheet. And, the Spreadsheet is opened in the dialog, and the active range is retrieved and converted to the range object. When the script is finished, the dialog is closed.

When above demonstration image is used, when you run the script, you can see the following values at the log. By this, you can confirm that the range ID can be converted to the range object.

[
  ["e2", "f2"],
  ["e3", "f3"],
  ["e4", "f4"],
  ["e5", "f5"],
  ["e6", "f6"]
]

Note:

• In this workaround, the dialog is used. So the user is required to open the Spreadsheet. Please be careful this.