Add Protocol URL links to relevant Asst tabs

[bthuronyi]

Settings & admin has links to lab-specific protocols shown on the Dashboard. Reproduce these (using formula references to the Dashboard works well I think) on appropriate Asst tabs in the header row.

[santiagochrist]

My goal is to create a one-size-fits-all piece of code that I can paste on every header. The idea is that the assistant tab will check the status of every entity with a sort number and compare it against the "status text shown when ready" values on the Dashboard. From there, output the protocol links and only take unique values so that you only have, for example, 2 protocols instead of 16 protocols on an assistant tab with 16 entities.

To start it off, I'm taking a very very simple approach and will build from there. the code below separates the status text with newlines and regexmatches it to dashboard text. Code is probably very inefficient, but it's a start. Will keep updating this thread as I learn more stuff:

=if(regexmatch(join(char(10),D3),Dashboard!$E17),"YES MATCH","NO MATCH")

[santiagochrist]

My copy is named "Santiago CC #254," and I'll be working on the issue there.

[santiagochrist]

Sounds promising?? Will document in a bit.

Code: =join(",",ARRAYFORMULA(IF(regexmatch(join(char(10),D3:D),Dashboard!$E5:E20), Dashboard!$E5:E20, "No Match")))

Output: No Match,No Match,No Match,No Match,No Match,No Match,No Match,No Match,No Match,No Match,No Match,No Match,ready to prep gel sample,No Match,No Match,No Match

EDIT: This indeed returns the keyword associated with what is written on the assistant tab Status. Now I just have to make it return the hyperlink with a protocol instead of the keyword, and finally unique it so that it doesn't spam text.

[santiagochrist]

This should clean up the output a little bit: Code: =join("",ARRAYFORMULA(IF(regexmatch(join(char(10),D3:D),Dashboard!$E5:E29), Dashboard!$E5:E29, ""))) Output: ready to quantready to prep gel sample

[santiagochrist]

It seems like pulling the hyperlink is very difficult to do without AppScript, so I'm gonna fiddle around with row number references to see if I can just access the link directly from the Settings Tab, if that makes sense. textjoin(delimiter,1,...) seems to remove the whitespace and only leaves me with numbers to work, which is really nice.

Code: =let(cellrow_numbers, textjoin(",", 1, arrayformula(IF(regexmatch(join(char(10),D3:D),Dashboard!$E5:E29), row(Dashboard!$D5:D29), ""))),cellrow_numbers) Output: 16,17

[santiagochrist]

So close. This outputs all protocol names but all of them have the same hyperlink.

Code: =let(cellrow_numbers, split(textjoin(",", 1, arrayformula(IF(regexmatch(join(char(10),D3:D),Dashboard!$E5:E29), row(Dashboard!$D5:D29), ""))),","), cell_names, split(textjoin(",", 1, arrayformula(IF(regexmatch(join(char(10),D3:D),Dashboard!$E5:E29), Dashboard!$D5:D29, ""))),","), arrayformula(hyperlink(index(settings_DashboardProtocolLinksList,cellrow_numbers-row($A$5)+1),cell_names)))

[bthuronyi]

Keep in mind that the URLs are stored in their own cells on Settings & admin! See column J.

[santiagochrist]

This was a much simpler fix than I thought it would be. Turns out that pulling the hyperlink is a lot easier than expected if we do indeed just reference Settings & admin. The below should work as long as we know which column has the sample statuses. In the case of Asst: dsDNA, it's in Column D. What I've done is define a status_range that should make the code more legible.

=let(
status_range,D3:D,
cell_names, split(textjoin(",", 1, arrayformula(IF(regexmatch(join(char(10),status_range),Dashboard!$E5:E29), Dashboard!$D5:D29, ""))),","),  
cell_links, split(textjoin(",", 1, arrayformula(IF(regexmatch(join(char(10),status_range),Dashboard!$E5:E29), 'Settings & admin'!$J2:J26, ""))),","),
arrayformula(HYPERLINK(cell_links,cell_names)))

[santiagochrist]

Nuances:

• Sometimes a status text can refer to more than one thing, so this code will pull all relevant protocols. Example: "ready to assemble" can be from d fragments or m donors, and so BOTH protocols will be outputted.
• As the code overwrites horizontally adjacent cells, it's important to place it in a cell with codeless adjacent cells.
• The code works very well for these assistant tabs: dsDNA, assemblies, golden gate. They all contain very easy to find statuses that refer back to protocols.
• The other assistant tabs don't really make use of a status and are more hands-off(?) with using them. For example, I don't really use the assistant tab for transformation as it mostly displays inputted data from the main transformation tab. What other tabs could make use of this code, if any?