Run a script as user, not author

[Revett]

I have a script that is triggered by a bot in my AppSheet app.  It always runs as me, the author.  It creates an email draft.  But I would like to have it create the draft in the user's email client rather than mine so that the user can make any modifications needed and then send it.  It is too complex to do using AppSheet's built-in email generator.  I have spent hours (and a lot of time with ChatGPT!) trying to resolve this, without success.  Anybody have any ideas?

[Steve]

It is not possible to use a script to affect an email draft.

[Revett]

I can create an email draft with no problem, but it is always in my email client and from me.  But I want it to be in the user's email client and from him/her.  I find it hard to believe it isn't possible, as it is easy to do in AppSheet, but creating the email content in this case is too complex to be done in AppSheet.

[scott192]

It is correct that Appscript Tasks run as the owner's email account.
Have you looked into Domain Wide Delegation

https://apps.google.com/supportwidget/articlehome?hl=en&article_url=https%3A%2F%2Fsupport.google.com...

It is something I intend to look into in the next few months and is intended to do something similar to your question regarding creating a draft in a user's gmail account using GmailApp.

[scott192]

Oh!  I have also just remembered that you can do some REALLY clever things by constructing a URL with parameters...for instance you could (instead of running a bot) make an external website open at the click of a button.  This external website would be https:mail.google.com...but you would extend that URL to auto fill from: to: cc: body:  and so on.  This URL would specifically open in the user's gmail.

https://stackoverflow.com/questions/6548570/url-to-compose-a-message-in-gmail-with-full-gmail-interf...

[Revett]

Good suggestions.  Thanks!  But I have decided that all this is somewhat beyond my skill set and in trying to implement some of the solutions I may screw up a whole app that otherwise works well.  (My workaround is to create the email as a doc and then have the user open a new email and cut and paste from the doc into the email.  Not exactly high tech, but it works.  Fortunately it only has to be done once or twice a day.  But it still offends me that my script can't just create the email on behalf of the user, particularly because it is the AppSheet user who invokes the script.  Ah well...)

[Revett · Accepted solution]

There is hope!  My buddy Claude.ai suggests adding a column to the main sheet in which the script puts a mailto link containing all the email's details, suitably encoded.  Example:

mailto:customer@example.com?subject=Your%20Order%20Confirmation&body=Dear%20Customer%2C%0D%0A%0D%0AT....

Then put an action button in the AppSheet app that opens this link.  The link could get pretty long as it contains the full text of the email, but apparently URLs up to 2,000 characters are not uncommon and my emails are likely to be 100 characters or less -- maybe 300 with encoding.

I'm going to give it a try.

[Revett]

It works!  A couple of restrictions: (1) The email is plain text, no formatting.  (2) The size limit is a factor.  The entire email -- addressee, cc, subject, and body -- has to be less than about 2,000 characters after URL encoding, which means a practical limit for the body of the email of about 1,000 characters.

[scott192]

That's a good start though!