0

I am using a Google spreadsheet as a 2x2 sparse matrix of permissions.

I have a header row "product name" and a header column "Employee name" (not necessarily in first position)

In the cells "product name"x"Employee name", I add the role granted to the user for the tool

This matrix is getting huge and it's hard to find data inside (when I CMD+F the tool name, and then that I CMD+F the person name, my view scrolls automatically and cannot focus on the "intersection of both, despite freezing the header column/row).

How can I easily improve this "find" workflow of finding the cell at the intersection of a product name and the employee name ? Please note that I need autocompletion : I do not know how the tool is spelled exactly (I might have multiple google products for example) and the same for employee name (I may have multiple users with same name).

Please note that the goal is to edit the actual value, not just find it

My matrix is like this

user/product Microsoft Office ... Microsoft teams
Michael Jordan manager
...
Michael Jackson simple access

I want to easily be able to scroll/filter the table to the matching cell(s) when I filter user:"mich" and product "microsoft"

How can I improve this find workflow in such a table ? I believe there are many ways to tackle this problem, but I'm not sure of how to go about any of those

  • Filtering of both rows AND columns
  • Custom script/formula to find cells and "Jump to" macro
  • Some sort of overlay widget
  • Custom query

Any help appreciated

2 Answers 2

1

Create two drop-down lists, one for the user range and another for the product range, and use address() and match() to get the A1 location of the cell at the intersection.

Assuming that your data table is in A4:Z and the two drop-down lists are in A2 and B2, try this in cell C2:

=let( 
  selectedUser, A2, 
  selectedProduct, B2, 
  matrix, A4:Z, 
  users, offset(matrix, 0, 0, rows(matrix), 1), 
  products, offset(matrix, 0, 0, 1, columns(matrix)), 
  address( 
    row(matrix) + match(selectedUser, users, 0) - 1, 
    column(matrix) + match(selectedProduct, products, 0) - 1, 
    4, true 
  ) 
)

To automatically jump to the cell at the intersection when first a user and then a product has been selected, use an onEdit(e) simple trigger, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  jumpToCell_(e);
}


/**
* Jumps to the cell whose address is in Sheet1!C2 when
* cell Sheet1!B2 is edited.
*
* @param {Object} e The onEdit() event object.
*/
function jumpToCell_(e) {
  let sheet;
  if (e.range.rowStart !== 2
    || e.range.columnStart !== 2
    || !(sheet = e.range.getSheet()).getName().match(/^(Sheet1)$/i)) {
    return;
  }
  const addressA1 = sheet.getRange('C2').getValue();
  sheet.getRange(addressA1).activate();
}

To get the value at the intersection, use a filter() nested in another filter(), like this:

=let( 
  selectedUser, A2, 
  selectedProduct, B2, 
  users, A5:A, 
  products, B4:Z4, 
  accessRights, A5:Z, 
  filter( 
    filter(accessRights, users = selectedUser), 
    products = selectedProduct 
  ) 
)

Alternatively, use the index(match()) pattern, like this:

=let( 
  selectedUser, A2, 
  selectedProduct, B2, 
  users, A5:A, 
  products, B4:Z4, 
  matrix, A4:Z, 
  index( matrix, match(selectedUser, users, 0), match(selectedProduct, products, 0) ) 
)
2
  • Hey this works nicely ! Now the only thing I'm missing, is a way to go to the cell value directly. Is there a way I can implement some sort of button or hyperlink that would let me go directly to the cell whose value is at Address(match(selectedUser, users, 0), match(selectedProduct, products, 0 ), 4, true) ? Also the current solution does not have autocomplet Feb 21, 2023 at 12:36
  • Edited the answer. The solution uses drop-down lists that support autocompletion. Feb 21, 2023 at 15:46
0

Building from @doubleunary's answer (please upvote it as well)

Assume you have 3 row "headers" and

  • a first sheet "authorizations" with
    • user first name and name on authorizations!C4:A
    • user last name and name on authorizations!B4:A

Create a second sheet to build full names (for easier autocompletion, assuming a couple first/last name is unique)

  • a second sheet "Users" with
    • a formula creating user first + last name on "Users!A4:4" (using same range to avoid confusion) =ArrayFormula('authorizations'!C4:C & " " & 'authorizations'!B4:B)

here is a formula to create a hyperlink to the target cell using the Address function (you need to replace your Spreadsheet and main sheet IDs in the formula)

=let( 
  selectedUser, B1, 
  selectedProduct, C1, 
  users, Users!A4:A, 
  products, D3:BS3, 
  accessRights, D4:BS, 
  Hyperlink(
    "https://docs.google.com/spreadsheets/d/[YOUR_SPREADSHEET_ID]/edit#gid=[sheet_id]&range=" & Address(match(selectedUser, users, 0)+2, match(selectedProduct, products, 0 )+3, 4, true), 
    Address(match(selectedUser, users, 0)+2, match(selectedProduct, products, 0 )+3, 4, true))
)

Please note that I have added 3 header rows so that I can integrate my "finder" formulas at the top left, here is what it looks like :

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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