Hello,

Together with some help of a developer, I was able to write some code that would extract the required info from a Google Calendar appointment, into Google Sheets as a reporting. Finally it sends a feedback form to the participants who did not decline the event, through gmail. 

For testing I used the “find event in calendar” function, as this returns certain data and when you run a test, you can use this Mockup data, to test your code. This all worked perfectly. 

Now when I put the code live, it doesn’t run anymore. It gives me the feeling, like it cannot be processed in a dynamic way (As in pulling the data and processing it, when it doesn’t know yet before what the data is going to be). As reporting happens after an event, I start with the “event ends” trigger, and actually the “find event” trigger would be redundant. When I removed the “find event” it caused more issues, as apparently not all variables are available for some reasons. 

Step 1 - Event ended in google calendar (Works)

Step 2 - Find the event in google calendar (does exactly the same as step 1, but enables the variable “attendeesResponses”) (Works)

Step 3 - Sent run javascript to code (Works) - here the data in AND the data out is correct. See code below. 

Step 4 - sent 1 new spreadsheet row to google sheets (this runs, but empty). The variables that were used, were the ones that were the output of the javascript and some generic ones that were already available from step 1 (time of the event, etc.). These variables were recognized by zapier as variables that could be selected, but were not filled with content. All rows remained empty. When I test the zap step by step it pulls up results in step 1, 2 and 3 and then fills these results into a spreadsheet row in step 4. This doesn’t happen when I turn the zap on, and the rows remain empty.  

Step 5 - Send an Email in Gmail to the recipients: output “email_not_declined” variable. >> Gmail hit an error, so the run has been rescheduled in the future. In the output of step 3 I see the emailadresses that the email should be send to (which is good). But somehow step 5 cannot access this variable, to actually send the email to these recipients and therefore it pauses the zap.  

→ First question: can I somehow skip step 2, as it is double and increases room for error?

→ Second question: does anyone know, why step 4 doesn’t access the data when the zap runs in a real-life setting, but it does when I test it step by step? (and of course how to solve this?)

Thank you! 

Code

Input data:

trainerEmail » (organizer Email in Google calendar)

attendeesEmails » (Attendee Emails in Google calendar)

attendeesResponses » (Attendees Response Status in Google calendar)

// 1.1 Create arrays from stringified input data 

var attendeesEmailArray = inputData.attendeesEmails.split(',');
var attendeesResponsesArray = inputData.attendeesResponses.split(',');

// 1.2 Create an array that contains objects with two fields: email and response.

var attendees = attendeesEmailArray.map((email, index) => ({
  email,
  response: attendeesResponsesArray index]
}));

// 1.3 Remove unwanted attendees (Trainer and the meeting room)
attendees = attendees.filter(attendee => {
  var email = attendee.email
  return (
    !email.includes('@resource.calendar.google.com') &&   
    email !== inputData.trainerEmail
  )
});

// 1.4 Count the sum of registrations and separate them based on their status
var countRegistered = attendees.length;

var countAccepted = attendees.filter(attendee => attendee.response === 'accepted').length;
var countDeclined = attendees.filter(attendee => attendee.response === 'declined').length;
var countTentative = attendees.filter(attendee => attendee.response === 'tentative').length;
var countNeedsAction = attendees.filter(attendee => attendee.response === 'needsAction').length;

// 1.5 an overview for quality check purposes and potential future use in Zapier
let emailRegistered = attendees.map(attendee => attendee.email).toString();
let overviewResponses = attendees.map(attendee => attendee.response).toString();

// 1.6 create a string with all the email addresses that have the status "accepted","tentative" or "needsAction"
var notDeclinedEmails = attendees.filter(attendee => attendee.response !== 'declined').map(attendee => attendee.email).toString();

// 1.7 create a string with all the email addresses that have the status  "declined"
var declinedEmails = attendees.filter(attendee => attendee.response === 'declined').map(attendee => attendee.email).toString();

// 1.8 define the output 
output = ={
  count_registered: countRegistered,
  count_accepted: countAccepted,
  count_declined: countDeclined,
  count_tentative: countTentative,
  count_needs_action: countNeedsAction,
  email_Registered: emailRegistered,
  email_not_declined: notDeclinedEmails,
  email_declined: declinedEmails,
  overview_responses: overviewResponses
}];