Need a Google Apps Script (Javascript) function to be written for a personality quiz

  • Posted:
  • Proposals: 10
  • Remote
  • #1924696
  • Awarded
OptiSol B.
The One Technologies T.HawksCode T.Tanzeel A.Joe I. + 5 others have already sent a proposal.
  • 8

Description

Experience Level: Intermediate
I need a Google Apps Script (i.e. Javascript) function to be written in Google Sheet to scores the results of a quiz and send an email the using the Mailchimp API.

I have created a personality quiz using Typeform. The purpose of the quiz is to identify certain belief patterns. There are 9 different belief patterns. You can see the quiz here: https://julieannegraham.typeform.com/to/aZ1IOh

I’m using Typeform’s Google Sheet integration. Everytime someone takes the quiz, their answers are automatically added as a new row in the Google Sheet by Typeform.

I’m also using Typeform’s Mailchimp integration so that the person’s email address is automatically added to my Mailchimp mailing list.

Every time a person completes the quiz, I need to identify the two top belief patterns they have based on the answers they gave in my quiz. I then need to email the person the results (using Mailchimp), with individual information on their top two belief patterns. This would be 2 individual PDFs. I finally need to enable an email sequence for that person in Mailchimp. This job is about creating a Google Apps Script function in Google Sheet that does this.

In order to identify the two top belief patterns, I have created a Scoring Map where each answer the person can give in my quiz is attributed to one or more belief pattern and given a certain weight for each belief pattern.

For example, if the person answered “It's hard to say no if other people need my help.” in the quiz, I would add 2 points to the “I’m responsible” belief pattern and 3 points to the “I’m not enough” belief pattern. I would do this for every answer the person gives and then calculate the total scope of each belief pattern based on the person’s answers. From this, I can find the two top belief patterns (the ones with the highest score).

Every time a new person completes my quiz, I need the results of this calculation to be added to the “Results” sheet of the workbook.

In order to achieve this, I need to have a Google Apps Script function written as per the following specs:

The function needs to run every 5 minutes. This can be done by configuring it using a Time-driven trigger (https://developers.google.com/apps-script/guides/triggers/installable)

Every time the function runs, it will find all the rows in the "Quiz" sheet that have not yet been added to the “Results” sheet. It will do this by using the value of the Token column, which uniquely identities a row.

For every new row in the “Quiz” sheet, the function will:

Add a new row in the Results sheet.

Calculate the two top belief patterns and set them in the “Primary Belief Pattern” and “Secondary Belief Pattern” columns of the Results sheet. To calculate the two top patterns, the function will simply look at all the answers given and calculate the top two patterns using the Scoring Map sheet.

Use the Mailchimp API to send the correct email to the respondent based on the two believe patterns found. The value of the “Email Status” column in the “Results” sheet must be set to:

“Sending” before starting the email sending process.
“Sent” once the email has been successfully sent.
"Failed" If an error occurred when sending the email. In that case, the “Note” column must be set to the error message.

Use the Mailchimp API to trigger an automation workflow. The “Email Workflow Status” and “Email Workflow Start Date” must be set appropriately depending on whether the operation succeeded or failed.

It’s of course possible that the Mailchimp API requests will fail for any number of reason. Every time the function runs, it must therefore check whether all previous email and email workflow operations been successfully done. If there was any failures, it must retry them.

Deliverables

- Source code of the Google App Scripts functions and of any automated tests
- Usage instructions
- A Google Sheets workbook with the function correctly setup and working

Acceptance Criteria

1. The function runs every 5 minutes
2. Whenever new rows are added in the “Quiz” sheet, the function adds corresponding new rows in the “Results” sheet
3. The values of the purple columns in the “Results” sheet are correctly copied over from the “Quiz” sheet
4. The two top belief patterns are correctly calculated and set in the Results sheet
5. The correct email is sent and the “Email Status” and “Email Sent Date” columns are set.
6. The Email Workflow is started and the “Email Workflow Status” and “Email Workflow Start Date” are set
7. If an error occurs with Mailchimp when trying to send the email, the function retries the next time it runs.
8. If an error occurs with Mailchimp when trying to start the email workflow, the function retries the next time it runs.

If you are interested in quoting for this job, please let me know and I can send you the Quiz and Results sheets along with the Scoring Map.

New Proposal

Create an account now and send a proposal now to get this project.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.