Google Docs, link to dynamic range from Google Sheets

Viewed 391
1 Answers

You can do the following:

  1. Create a new named range that includes the current range of the table (ref: Name a range of cells).
  2. Link that named range to your desired location, using Link to data in a spreadsheet.
  3. On your spreadsheet, click Tools > Script editor to open a bound script, and copy the following code. This function retrieves your desired named range and updates it with the current dimensions of your table:
const SHEET_NAME = "Sheet1"; // Change according to your preferences
const NAMED_RANGE_NAME = "MY_RANGE"; // Change according to your preferences

function updateNamedRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  const namedRanges = ss.getNamedRanges();
  const namedRange = namedRanges.find(namedRange => namedRange.getName() === NAMED_RANGE_NAME);
  const range = sheet.getRange("A1").getDataRegion();
  namedRange.setRange(range);
}
  1. Install a time-driven trigger which will fire updateNamedRange with the specified periodicity. You can do that manually (following these steps), or programatically. In order to install this programmatically, copy and run a function like this once:
function installTimeTrigger() {
  ScriptApp.newTrigger("updateNamedRange")
  .timeBased()
  .everyMinutes(1)
  .create();
}

Note:

  • The previous sample is using everyMinutes with the parameter set to 1, so the range will be updated every minute. You can find methods for alternative frequencies here.
  • In the sample above, the table is in a sheet named Sheet1, with a named range title MY_RANGE, and it starts at cell A1. Change all those in your spreadsheet if that's not your case.
  • I'm assuming you already know how to link named ranges.
Related