We are building a social, emotional, and behavioral assessment inspired by the BESSI 96, which evaluates 32 skill facets within six skill domains. Currently, we have a Survey Monkey form collecting user data and answers to 96 questions, which we want to be inputted into a Google Sheet for scoring through Zapier or a different automation service.
There are two main challenges we face:
-
Collecting formula-based scores from Google Sheets, as Zapier cannot retrieve formula values.
-
Ensuring that each participant’s scores are not affected by others’ responses by creating separate instances of the scoring sheet.
Desired Outcome
Upon receiving a response in our Survey Monkey form, we want to:
-
Calculate the scores for the 32 skill facets and six skill domains in Google Sheets.
-
Integrate the scores into a Google Doc with custom visualizations, definitions, and a personalized story generated by the OpenAI model, highlighting the user’s strengths and weaknesses.
-
Convert the Google Doc into a PDF and email it to the user through Mailchimp, integrated with Zapier automation.
We’ve attempted to use Google Apps Script to automatically copy cell values when they’re updated. While this works for manual edits, it doesn’t function when updates come from Zapier events, as Zapier utilizes the Google Sheets API. We’re currently exploring alternative automation software that can read formula values to address this issue.
Our second challenge, creating unique scoring sheets for each participant, remains unsolved. We’ve considered generating a new sheet for each response, but this approach raises two concerns:
-
How can we effectively manage the deletion of these sheets once they’ve scored the responses?
-
How can we accurately target the row to be updated in the automation if a new sheet copy must be created first?