Appsheet Formula in GScript JSON

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 :slight_smile:

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,

}
]
};

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

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”)”

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

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

@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

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

What do you mean?

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

How are you triggering your Google Apps Script?

on a timer

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.

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.

// 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

Thank you. I will give this a shot.