Google Sheet Cell Hyperlink (Not Just Text)-CodePudding

I would like to make the entire cell clickable in Google Sheets, not just text. This is so that when I paste the whole content into Gmail, I hope it will maintain the same format. I found some documentation regarding MS Excel but it doesn't quite apply to Google Sheets.

The current Google Sheets look like the image below with the hyperlink text underlined. When pasting it inside Gmail, I just get a dark blue "Pay Invoice" hyperlink text. The button format in Google Sheets is just a cell containing the hyperlink text with a blue background fill color and white text.

I wanted to make the entire box clickable without any underlining text.

If it's not possible, can I create this effect in HTML using Google App Script?

CodePudding user response：

This pretty much requires HTML formatting and you cannot embed HTML within Google Sheet cells. You can, however, use Apps Script to create a

You can just copy and paste this into Gmail and it should keep the format. Alternatively you could create a function to show a

The dialog is shown on top of the Sheet interface and you should be able to copy it as well. Depending on your HTML, your result should look fine after pasting it in Gmail:

Now, you just asked for something to copy and paste, but I'm guessing that you're trying to email a table copied from Sheets with the full invoice, so you could consider using templates. With this you could create an HTML template with printing scriptlets and just automatically generate the table by pulling the data from your Sheet, then send it as an email with sendMail(). For instance, the button portion from index.html:

  <div >
    <a href="<?=getURL()?>">
      <div id="button">
        PAY INVOICE
      </div>
    </a>
  </div>

The scriptlet <?=getURL()> calls your server-side function getURL(), which you can use to return a URL saved in a cell on your Sheet:

//Code.gs
function getURL(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var range = sheet.getRange("A1") 

  return range.getValue() //returns the URL in A1
}

Then you can have a function to build the template and send it through email as HTML, which would look just like the output from the doGet() above:

//Code.gs
function sendEmail(){
  var html = HtmlService.createTemplateFromFile('index').evaluate().getContent()
  MailApp.sendEmail({to:"[email protected]", subject:"Test", htmlBody:html})
}

This is just a basic sample. It would take some work to make a good template but I hope it gives you an idea of what you can do with Apps Script. There's a lot more to see in the documentation.

Sources: