How to send google-sheet data in an email keeping the formatting( colors) intact from python?

Question

I have a requirement where I have to send the google-sheets data in an email as a table with all the color formatting in the sheet.

Previously I was reading the data from the sheet, loading it pandas data frame, and then applying color coding there. since I have many reports generated in google-sheets that need to be emailed, it is not feasible. Want to know if this can be done using google API? Can't consider extension too.

In your situation, I have created a Google Apps Script library for converting the cell values to HTML data. [Ref](https://github.com/tanaikech/RichTextApp) Because in the current stage, the text style of the part of texts in a cell cannot be retrieved using Sheets API. From this, for example, how about using Web Apps created by Google Apps Script as a wrapper API? By this, I think that your goal can be achieved. If this was not the direction you expect, I apologize. — Tanaike, Jan 21 '22 at 00:40
Thank you for replying. If that was not the direction you expect, I apologize. — Tanaike, Jan 21 '22 at 06:56
Can I ask you about the detail of the issue of my proposed method? — Tanaike, Jan 24 '22 at 07:19
Can you please explain why it is not feasible? I know you mentioned that you have many reports generated in google-sheets that need to be emailed, but I'm not understanding the real issue why you can't keep doing what you were doing before. Is it because of volume of API calls? anything else? thanks — nferreira78, Jan 27 '22 at 14:45

score 1 · Answer 1 · edited Jan 28 '22 at 14:36

I belive it can be done usng Google API, as you can export any sheet to html format using a HTTP request GET https://www.googleapis.com/drive/v3/files/fileId/export

Check this link: https://developers.google.com/drive/api/v3/reference/files/export

You can test it on that page, just pass Your Document's id and specify mime type as application/zip to get Your sheet as a zipped html. See here for other approved mime types: https://developers.google.com/drive/api/v3/ref-export-formats

score 1 · Answer 2 · answered Jan 28 '22 at 15:04

You can do it by script as follows

function sendEmail() {
  MailApp.sendEmail({to:'somebody@gmail.com',
  subject: 'my subject', 
  htmlBody: tableHTML()})
};
function tableHTML(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  var plage = sh.getDataRange()
  var data = plage.getDisplayValues()
  var taille = plage.getFontSizes()
  var fond = plage.getBackgrounds()
  var couleur = plage.getFontColors()
  var police = plage.getFontFamilies()
  var htmltable = '<table style="border:1px solid #CCC;border-collapse:collapse;">';
  for (row = 0; row<data.length; row++){
    htmltable += '<tr>';
    for (col = 0 ;col<data[row].length; col++){
      if (data[row][col] === "" || 0) {htmltable += '<td>' + '&nbsp;' + '</td>';} 
      else
        htmltable += '<td style="border:1px solid #CCC; font-family:' + police[row][col] + '; background-color:' + fond[row][col] + '; color:' + couleur[row][col] + '; font-size:' + taille[row][col] + 'px;">' + data[row][col] + '</td>';
      }
      htmltable += '</tr>';
    }
  htmltable += '</table>';
  return htmltable
}

Piotr Kocybik · Answer 3 · 2022-01-30T10:23:19.787

It's doable. All You need to do is to specify the correct scope when authenticating, https://spreadsheets.google.com/feeds, https://docs.google.com/feeds and https://www.googleapis.com/auth/drive.file

First, let's see how easy it is for a file with "anyone with link" permissions. We will be calling the API from a browser window.

– Get Your API Key, from Google developers Console (create the app, give it access to Drive API and generate API Key.)

– Get a File ID of a google spreadsheet with permissions set to "Anyone with the link".

Now paste this address to You browser to get a zip archive with your sheet in html format along with a css file: https://www.googleapis.com/drive/v3/files/{FILE_ID}/export?mimeType=application%2Fzip&key={YOUR API KEY} Remember to fill the adress with Your FILE_ID and an API_KEY.

Now let's do the same on a private file using curl and OAuth authentication.

0. Prepare Your API_KEY, CLIENT_ID and CLIENT_SECRET from Google Developer Console.

1. Verify device and get DEVICE_CODE

curl \
-d 'client_id=[CLIENT_ID]' \
-d 'scope=https://spreadsheets.google.com/feeds https://docs.google.com/feeds https://www.googleapis.com/auth/drive.file' \
https://oauth2.googleapis.com/device/code

2. Go to verification url https://www.google.com/device

3. get the ACCESS_TOKEN

curl -d client_id=[CLIENT_ID] \
-d client_secret=[CLIENT_SECRET] \
-d device_code=[DEVICE_CODE] \
-d grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code \
https://accounts.google.com/o/oauth2/token

3. OR Refresh the token

curl \
--request POST \
--data 'client_id=[CLIENT_ID]' \
--data 'client_secret=[CLIENT_SECRET]' \
--data 'refresh_token=[REFRESH_TOKEN]' \
--data 'grant_type=refresh_token' \
https://accounts.google.com/o/oauth2/token

4. AND GET YOUR FILE

curl \
-H 'Authorization: Bearer [ACCESS_TOKEN]' \
-H 'Accept: application/json' \
https://www.googleapis.com/drive/v3/files/[FILE_ID]/export?mimeType=application%2Fzip&key=[API_KEY]

Will this fetch me the file with all the color formatting in sheet ? — Atif, Jan 30 '22 at 09:52
Yes, even without the attached css file in a zip, wich is heavy, the html has cell backgroud colors and other styles inline-emedded. I have just made a correction to the original post, the solution does not require the use of restricted scopes, so no google vverification id required. — Piotr Kocybik, Jan 30 '22 at 10:27

How to send google-sheet data in an email keeping the formatting( colors) intact from python?

3 Answers3

Now let's do the same on a private file using curl and OAuth authentication.

Linked