How do I Send Email From Retool Table w/ GMail API

Looking for some help to walk through steps of successfully enabling GMail API to send emails from Retool table.

I’ve followed a few threads (including this one [How to connect to Google APIs]) but continue to run into issues in successfully implementing.

From my Google Console, I’ve created credentials w/ OAuth2.0 access, copied key and password into the form in appropriate places when adding a resource to Retool, and added scopes as needed.

When I save the resource, add to a query in Retool, and attempt to re-authenticate API, I’m given an error from Google “ERROR 400: Invalid_Request. Missing required parameter: redirect_uri”

Attempting to troubleshoot, I find no area within my REST_API resource to add a Redirect_API or have found a redirect_URI provided by Retool to input in my GoogleCredentials when creating a ClientKey as I’ve seen available in other tutorials[https://www.youtube.com/watch?v=4x2TVgLZjWo]

I’m rather new to setting up APIs so I apologize in advance if I’m missing something super simple. Any help would be greatly appreciated in helping me solve.

-Haden.

A simpler solution would be to call a Zapier or Integromat webhook which sends out your Gmail for you

Thanks @byron. I had actually just restarted from scratch after posting and was able to successfully connect and re-authenticate in query editor. However I’m now getting a different error when running the query. I believe it may have something to do with my base_URL and POST methods in the query editor.

Trying to fumble through this one.

Returning error “‘raw’ RFC822 payload message string or uploading message via /upload/* URL required”

Have attached screenshot w/ query and error message, I have not had luck finding answer in stack forums or continuing to read through GMail API documentation. Any help is greatly appreciated.

Hey @hadenp92, I was building out an internal Retool application that used the Gmail API and ran into the same issue. I solved it by adapting Google’s send.js example code.

First, you’ll want to create a Transformer to encode your inputs into a base64-encoded message. Using the inputs your provided in the screenshot, it should look something like this:

const to = {{table1.selectedRow.data.Email}}
const subject = 'Your subject'
const body = 'Message body'
const from = 'from-address@example.com'

const utf8Subject = `=?utf-8?B?${btoa(subject)}?=`;
const messageParts = [
  `From: ${from}`,
  `To: ${to}`,
  'Content-Type: text/html; charset=utf-8',
  'MIME-Version: 1.0',
  `Subject: ${utf8Subject}`,
  '',
  body,
];
const message = messageParts.join('\n');

// The body needs to be base64url encoded.
const encodedMessage =  btoa(message)
  .replace(/\+/g, '-')
  .replace(/\//g, '_')
  .replace(/=+$/, '');

return encodedMessage

You’ll then want to update your query. The Body section should have a single raw parameter set to the value of your transformer. I’m assuming that the transformer is named transformer1.

Using this setup, I was able to successfully send an email. I could not figure out how to support the snippet parameter. I also didn’t see the contact_name parameter documented for send

2 Likes

@kyle

You are a life saver, thank you so much for the detailed write up and sharing. I just created the transformer and adjusted the raw parameter you suggested and email came through correctly. Now just need to work on the format a bit, assuming I can do this in the transformer at the 'Content-Type: text/html; charset=utf-8 section?

Thanks again for your help! :raised_hands:

1 Like

Yep! You can change the content of the email by updating the body string. Pro-tip: You can use template strings for multi-line strings

const body = `This is a multi-line email body.

It almost looks like a template.

No need to make different variables for the content`
2 Likes

Hey @kyle !

I’m still working through this one but thought maybe I’m overlooking something simple.

My body string I’m trying to include in email is a {{textinput2.value}} string from a component in the tool. Following your example, the body still comes out with each line having been joined. Am I missing something else?? I’m understanding the const message = messageParts.join('\n'); to be needed for encoding to base64url so this join will need to remain correct. Is there a function i should include in the const body : variable??

Hello all,

As a followup, if you’re interested in sending attachments with the Gmail API you’ll need to use a different endpoint and slightly different formatting.

Google docs: https://developers.google.com/gmail/api/guides/uploads

Transformer:

Query:

EDIT:
Copyable transformer code:

const to = {{to.value}}
const from = {{from.value}}
const subject = {{subject.value}}
const body = {{body.value}}

const fileType = {{filepicker1.file.contentType}}
const fileData = {{filepicker1.file.data}}
const fileName = {{filepicker1.file.name}}
  

const messageParts = [
  'Content-Type: multipart/mixed; boundary="message_section"',
  'MIME-Version: 1.0',
  `From: ${from}`,
  `To: ${to}`,
  `Subject: ${subject}`,
  
  '--message_section',
  'Content-Type: text/plain; charset="UTF-8"',
  'MIME-Version: 1.0',
  'Content-Transfer-Encoding: 7bit',
  '',
  body,
  '',

  '--message_section',
  `Content-Type: ${fileType}`,
  'MIME-Version: 1.0',
  'Content-Transfer-Encoding: base64',
  `Content-Disposition: attachment; filename=${fileName}`,
  '',
  fileData,
  '',
  '--message_section--'
];

return messageParts.join('\r\n');
4 Likes

Hello, I am trying to attach a file to an email using Gmail API! I followed the example above, however this is what I get as an error ( Attached image)

Thank you for helping in advance

Would you mind sharing the rest of the view of that query which is hidden under there error there? The base url will be a bit different for sending emails with files (can’t really say why the gmail api is set up like this), and you’ll need to be sure to add a header of Content-Type : message/rfc822

I updated Alexi’s reply here to include the new full transformer code as well for comparison!

Hey Alex, thanks for replying! I have used the transformer as a baseline and still get the error. Here is the rest of the query:

Hey Gene! Happy New Year!

Would you mind sharing a screenshot of the transformer code you have set up, and the return of transformer4.value in the left panel of the editor?

Running into this error as well, but was able to get something going by combining the two previous responces.

const to = {{to.value}}
const from = {{from.value}}
const subject = {{subject.value}}
const body = {{body.value}}

const fileType = {{filepicker1.file.contentType}}
const fileData = {{filepicker1.file.data}}
const fileName = {{filepicker1.file.name}}
  

const messageParts = [
  'Content-Type: multipart/mixed; boundary="message_section"',
  'MIME-Version: 1.0',
  `From: ${from}`,
  `To: ${to}`,
  `Subject: ${subject}`,
  
  '--message_section',
  'Content-Type: text/plain; charset="UTF-8"',
  'MIME-Version: 1.0',
  'Content-Transfer-Encoding: 7bit',
  '',
  body,
  '',

  '--message_section',
  `Content-Type: ${fileType}`,
  'MIME-Version: 1.0',
  'Content-Transfer-Encoding: base64',
  `Content-Disposition: attachment; filename=${fileName}`,
  '',
  fileData,
  '',
  '--message_section--'
];

const message = messageParts.join('\n');

const encodedMessage =  btoa(message)
  .replace(/\+/g, '-')
  .replace(/\//g, '_')
  .replace(/=+$/, '');

return encodedMessage

This allows the email to be sent with the attachment, but then the body does not get included in the email. My query is identical to @GeneCarla's.