0

I have a simple query that outputs columns A and B of a project dataset, when the value in column A is not empty:

=QUERY(Projects!A2:B, "SELECT A,B WHERE A<>''")

This creates an easy to scan highlight of the important project data (in my case, project name and next action item). However, the project details are in another tab, so if I want to examine the project, I have to copy the name, go to the tab, and search for the project.

It would be super nice if the output of the QUERY could automatically hyperlink to the source data. Or maybe there is a hotkey I can press to jump to the source. Does this feature exist?

1
1

You have some project transaction data that you query. For each project, there is additional static data in another sheet. When you query your transaction data you want to create a hyperlink to the relevant project on the project sheet.

You have a query that you already use: =QUERY(Projects!A2:B, "SELECT A,B WHERE A<>''"). Insert this in your output sheet in cell A1

  • From your project details sheet, copy the sheet url up to and including the gid. This will look like this. "https://docs.google.com/spreadsheets/d/1tvshJ...../edit#gid=1448462430"

  • Paste the url into the following formula replacing the section labelled "<>"

  • Insert this formula in cell C1, beside the query: =array_constrain(ARRAYFORMULA({HYPERLINK("https://docs.google.com/spreadsheets/d/<<insert sheetID and gid here>>&range=A"&match(A1:A,Project_details!A2:A5,0)+1,"Link to "&A1:A)}),COUNTA(A1:A),1)


Explanation:

  • The formula creates a hyperlink for the project name in Column A.
  • The hyperlink url is built in three parts:
    • the main body is the url of the project details sheet. You have copied/pasted this into the formula.
    • the column of the linked range is provided by &range=A
    • the row of the linked range is provided by the match function. Note that the actual function is match +1; the "1" allows for the header row on the product details sheet.
  • the formula is wrapped in an arrayformula so that it automatically calculates results for every row.
  • array_contrain is used to limit the output. If not used, the arrayformula would calculate for every row of the sheet - even though it might display output in the rows where there is a value in Column A

Say you have sheet called "Projects" that contains project transaction data. The sheet might look like this:

Project Transaction data

Project transaction data


You also have a sheet containing further static details of each project. Say, this sheet is called "Project_details" and it might look like this:

Project details

Project details


Your output sheet will contain two formula; one in Cell A1 and another in Cell C1. The output should look like this:

Query output and hyperlink

Query output and hyperlink


2
  • Nice. I ended up using Action Script to accomplish this, but it would have been nice to do it without needing a script – matt snider Jul 13 '20 at 4:48
  • Thanks, and well done. You might want to publish your script as an answer too. It's always good to see that there can be multiple solutions to a question; and it can help others who may come looking for answers in the future. – Tedinoz Jul 13 '20 at 5:03
0

I like Tedinoz's solution, but had come up with an Action Script solution as well, which I'll share for posterity.

This code is specific to my sheets, but it should be generalizable. The key part is set the value of a range to a hyperlink of "#gid=<sheet_id>&range=":

rangeLinkOutput.setValue('=hyperlink("#gid=' + projectsSheetId + '&range=B' + project.row + '";"link")');

Here is the complete code:

// Holder for project related information.
class Project {
  constructor(name, actionItem, row) {
    this.name = name;
    this.actionItem = actionItem;
    this.row = row;
  }
}

const bold = SpreadsheetApp.newTextStyle().setBold(true).build();

// Generates the action item page and hyperlinks.
function generateActionItems() {
  // Pointer constants.
  const spreadsheet = SpreadsheetApp.getActive(),
        actionItemsSheet = spreadsheet.getSheetByName("Action Items"),
        projectsSheet = spreadsheet.getSheetByName("Projects");

  // Sheet details constants.
  const projectsSheetId = projectsSheet.getSheetId(),
        lastRowProjects = projectsSheet.getLastRow(),
        lastRowActionItems = actionItemsSheet.getLastRow();

  // Positional constants.
  const startRow = 2, // Post header row.
        projectsProjectColumn = 1,
        projectsActionColumn = 2,
        actionItemsSheetActionItemColumn = 1,
        actionItemsSheetHyperlinkColumn = 2;

  // Sort the projects
  let projects = [];
  for (let row = startRow; row <= lastRowProjects; row++) {
    let range = projectsSheet.getRange(row, projectsProjectColumn);

    if (!range.isBlank()) {
      projects.push(new Project(
        range.getValue(),
        projectsSheet.getRange(row, projectsActionColumn).getValue(),
        row
      ));
    }
  }

  // Orders projects by priority, then project name - modifies the array.
  projects.sort((a, b) => {
    return a.name.localeCompare(b.name);
  });

  // Output the array.
  let insertRow = startRow;
  projects.forEach(project => {
    // Output for action items sheet.
    const rangeActionItemOutput = actionItemsSheet.getRange(insertRow, actionItemsSheetActionItemColumn),
          rangeLinkOutput = actionItemsSheet.getRange(insertRow, actionItemsSheetHyperlinkColumn),
          actionItemText = project.name + ": " + project.actionItem,
          actionItemRichText = SpreadsheetApp.newRichTextValue()
            .setText(actionItemText)
            .setTextStyle(project.name.length + 1, actionItemText.length, bold)
            .build();
    rangeActionItemOutput.setRichTextValue(actionItemRichText);
    rangeLinkOutput.setValue('=hyperlink("#gid=' + projectsSheetId + '&range=B' + project.row + '";"link")');

    // Increment the row to updated.
    insertRow++;
  });

  // Remove extra rows.
  for (let row = insertRow; row <= lastRowActionItems; row++) {
    const rangeActionItemToDelete = actionItemsSheet.getRange(row, actionItemsSheetActionItemColumn, 1, 2);
    rangeActionItemToDelete.setValue("");
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.