Appsheet Formula in GScript JSON

AppSheet AppSheet Q&A
1

Hi all,

Does anyone know if it’s possible to have an appsheet formula in a google script when invoking action:Add? I would like to have extractemails() for my var senderDetails.

Thanks

Here’s my payload:
var body = {
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,
},
“Rows”: [
{
“Communication Method”: “Email”,
“Communication”: bodyContents,
“Thread ID”: threadID,
“Message ID”: ID,
“Reply To”: senderDetails,
“To”: receiverDetails,
“CC”: CCDetails,
“BCC”: BCCDetails,
“Subject”: subjectText,
“Sent timestamp”: dateTime,

}
]
};

2

You can specify your function directly to the JSON param

var body = {
	“Action”: “Add”,
	“Properties”: {
	“Locale”: “en-US”,
	“Location”: “47.623098, -122.330184”,
	“Timezone”: “Pacific Standard Time”,
	},
	“Rows”: [
		{
		“Communication Method”: “Email”,
		“Communication”: bodyContents,
		“Thread ID”: threadID,
		“Message ID”: ID,
		“Reply To”: extractemails(),
		“To”: receiverDetails,
		“CC”: CCDetails,
		“BCC”: BCCDetails,
		“Subject”: subjectText,
		“Sent timestamp”: dateTime
		}
	]
};
1 Like
3

Hi @LeventK

Thanks, would this method work in google scripts as well? I got an error: “ReferenceError: extractemails is not defined (line 156, file “Code”)”

4

Might try making a virtual column defined as ExtractEmails() and include that in your call.

5

The topics you are dealing with on this your thread is highly complicated, not easy to discussed and solved…on the community place alone. I would suggest you go to the path to discuss through the partners like Able3, @MultiTech_Visions or other skillful Appsheet partners who are familiar with those kinds of stuffs. Not me.

2 Likes
6

@tingtingandrea
I have assumed that you have already a defined function in your script named as extractemails(). If you don’t have such a function, then the received error is so normal

1 Like
7

LeventK:

unction, then the recei

@LeventK, how would I define the usual appsheet functions in a google script? Would I need to write them out manually?
Thanks

8

tingtingandrea:

I define the usual appsheet functions in a google script?

What do you mean?

9

LeventK:

extractemails()

extractemails() is a formula in appsheet right? If I want to use it in my payload in google script. How can I do this?

10

How are you triggering your Google Apps Script?

11

on a timer

12

And what the script originally does? The given payload is only a part of it I assume. I understand that it adds rows to a table, but how and from where you gather the data? Please be specific.

13

We pull from our gmail so it returns [to] and we need to only extract the emails out since gmail will bring it in with names and email.

14 
// extract emails from label in Gmail
function extractEmails() {
  // get all email threads that match label
  var lbl = "some label text";
  var threads = GmailApp.search ("label:" + lbl);
  
  // get all the messages for the current batch of threads
  var messages = GmailApp.getMessagesForThreads (threads);
  
  var emailArray = [];
  
  // get array of email addresses
  messages.forEach(function(message) {
    message.forEach(function(d) {
      emailArray.push(d.getFrom(),d.getTo());
    });
  });
  
  // de-duplicate the array
  var uniqueEmailArray = emailArray.filter(function(item, pos) {
    return emailArray.indexOf(item) == pos;
  });
  
  var cleanedEmailArray = uniqueEmailArray.map(function(el) {
    var name = "";
    var email = "";
    
    var matches = el.match(/\s*"?([^"]*)"?\s+<(.+)>/);
    
    if (matches) {
      name = matches[1]; 
      email = matches[2];
    } else {
      name = "N/A";
      email = el;
    }
    
    return [name,email];
  }).filter(function(d) { // remove filter param if you don't want to remove away any email adress from the array
    if (
         d[1] !== "levent@able3ventures.com" &&
         d[1] !== "noreply@able3ventures.com" &&
         d[1] !== "able3@able3ventures.com"
       ) {
      return d;
    }
  });
}

TO YOUR PAYLOAD

var body = {
	“Action”: “Add”,
	“Properties”: {
	“Locale”: “en-US”,
	“Location”: “47.623098, -122.330184”,
	“Timezone”: “Pacific Standard Time”,
	},
	“Rows”: [
		{
		“Communication Method”: “Email”,
		“Communication”: bodyContents,
		“Thread ID”: threadID,
		“Message ID”: ID,
		“Reply To”: cleanedEmailArray[1].toString(),
		“To”: receiverDetails,
		“CC”: CCDetails,
		“BCC”: BCCDetails,
		“Subject”: subjectText,
		“Sent timestamp”: dateTime,

		}
	]
};
2 Likes
15

Thank you. I will give this a shot.