Google-sheets – Google Sheets to Gmail Search

formulasgmail-searchgoogle sheets

Basically I store order data in a google sheet.

The Rows are in numerical order corresponding to the order number, is there a way to link clicking on the order number to a gmail search result?

Specifically I'd like to click on the order number cell in Sheets, and trigger a new tab or page, with the gmail search results for the selected order number.

Is this possible?

Best Answer

Can it be done. Yes. I've just been through this myself, and can offer some thoughts.

I created a link using a HYPERLINK formula. I made the link part of a template, as well as other links that were inserted by a script (cell.setFormula('=HYPERLINK("'+URL+'","Gmail Lookup")');). You would need to do the same, it's not just a case of magically clicking your order number.

Gmail has several search operators and these can be easily incorporated into a formula. But there's not a lot of documentation/precedents for the structure of the URL, so I experimented in Gmail and noted how the URL changed for different search terms, and then applied those "stub URLs" back to my Sheets formula.

You don't explain the relationship between your order number and gmail so I'll demonstrate three methods that I used.

Search by Label

If you are using sub-labels, then you must search by the complete label (including parents)

  • sample label = "IMatch IMatch Forums" where "IMatch" = parent label, and "IMatch Forums" = sub label
  • convert to lower case
  • substitute "-" (dash) for spaces
  • Stub URL = https://mail.google.com/mail/u/0/#label/

Search by Subject

Search by Word/Multiple words

  • sample word = VA53297
  • sample adjacent words = quick brown fox
  • convert to lower case
  • substitute "+" (plus sign) for spaces
  • enclose in quotations marks (not strictly necessary for single words)
  • Stub URL = https://mail.google.com/mail/u/0/#search/"
  • note: there is a quotation mark that is part of the Stub URL. This is in addition to enclosing the search words in quotes

enter image description here

Related Topic