I've set up a Google spreadsheet so that it creates a new draft message in Gmail and copies the contents of a sheet into it.

However, when the sheet contents are copied over, it only copies the values and not the formatting such as background colors.

Here's what I have.

function sendEmail() {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Get the data from the sheet
  var data = sheet.getDataRange().getDisplayValues();

  // Create a new email
  var subject = "Shift Paperwork";
  var body = "";

  for (var i = 0; i < data.length; i++) {
    body += data[i].join("\t") + "\n";

  // Open the email draft
  var draft = GmailApp.createDraft("", subject, body);

  // Show a success message
  Browser.msgBox("Email draft created successfully.");

1 Answer 1


getDisplayValues, as the method name suggests, only returns the display values.

If you found this confusing, please understand that in the spreadsheet jargon, the term display values refers to the chain of characters shown on a cell / range; it doesn't include

  • Text styling format like bold, italics, font, font color, etc.
  • Cell background color, cell borders, etc.

To create a formatted email message from the content of a spreadsheet, you should produce an HTML formatted message.

Please learn the jargon, JavaScript, and HTML and develop your skills. This will help you to understand Google Apps Script better.

Also, please learn about the web apps you will be automating with Google Apps Script, in this case, Google Sheets and Gmail.

  • I understand that the getDisplayValues function only returns display values, as the name suggests. However, I am unsure of how to modify the code to meet my requirements. Learning JavaScript, HTML, and the related jargon is a long-term solution, but I was hoping to receive a response on this forum that could be applied in the short term. I expected to ask questions and receive answers that were actionable within a reasonable time frame. Feb 14 at 16:07
  • @EvanBallard Your expectation is fine; remember that this is not a help/service desk. There are no promises about when a question will get "the perfect answer". It would be best to do something in the meantime, like learning what was suggested or doing other things. One option is that you make some research efforts; in such case, consider updating your question to make it more specific, or if you find "the solution", please consider to post it as an answer. P.S. I'm using an AI add-on to help improve my writing. Feb 14 at 16:10
  • I.E., you might look at the link shared by Blindsposts in a comment to the question and share with us why it was not helpful if you are still looking for an answer to this question. Feb 14 at 16:19
  • Thank you for your help, Rubèn. I will look into the link shared and continue researching. Feb 21 at 15:43

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.