Save Tally questions to Google Sheets

Questions
, , , , , ,
1

:bullseye: What is your goal?

Save the question keys or labels from a Tally questionnaire as header row in Google Sheets

  • I would like to stick to native Make, not having to pay for external modules.
  • A solution where a mismatch between values and the keys of a question is unlikely would be preferred.
  • I ideally would want to have a solution where I do not need to name the exact fields, but allow for the Tally questions to be extended, moved, deleted or adjusted. Matching by name would be ideal, though I doubt that’s possible. Thus, I do not need those changes to work with existing Google sheets, though - assume that if anything changes in the Tally questionnaire, the user makes sure there is a new empty Google Sheet ready for those changes.

:thinking: What is the problem & what have you tried?

I am having trouble retrieving the question field names from a Tally questionnaire to save them into a the header row of a Google sheet once the first response comes in. By that, I mean the phrasing of a Tally question, e.g. “Name”.

What I tried:

  • Using the Tally webhook and parsing its contents. I can retrieve the questions using {{get(keys(39.fields); 2)}}. 39 is my webhook module. Then, I can retrieve the corresponding values using {{get(39.fields; get(keys(39.fields); 2))}}. However, the json which Tally sends via the webhook includes the questions in 3 different formats, resulting in more fields than is useful. Especially for questions which allow for multiple answers such as CHECKBOXES and MULTIPLE_CHOICE, questions are parsed individually as booleans in addition to the more useful array question field.
  • Using the Tally webhook “normally” only returns the field values, not the names of the fields.
  • Using the List Fields Definitions Module by Tally - same issue, the structure of the questions is no better.
  • Using a standard webhook - same issue, the output doesn’t vary
  • Using an HTTP request to retrieve the questions only via the Tally API (Beta) https://api.tally.so/forms/my_id/questions - it returns the questions, but the JSON isn’t simple, it shows much more. The advantage here is that no unwanted boolean questions appear. It looks like this, with the wrapper:
    [
    {
    “statusCode”: 200,
    “headers”: [...],
    “cookieHeaders”:[...],
    “data”: {...},
    “fileSize”: 13124
    }
    ]
    And data is filled with a questions dictionary, documented here: Listing questions - Tally Developer Docs and the “title” is the correct field I’m looking for.

Here is some more background on how the Tally webhook parses the data:
Excerpt showing CHECKBOXES data from the middle of the fields key:

the previous question would be noted here
“Some question”: [
“Option A”,
“Option B”,
“Option C”,
“Option D”,
“Option E”
],
“Some question (Option A)”: true,
“Some question (Option B)”: true,
“Some question (Option C)”: true,
“Some question (Option D)”: true,
“Some question (Option E)”: true,
“Some question (Option F)”: false,
“Some question (Option G)”: false,
“Some question (Option H)”: false,
“Some question (Option I)”: false,
and then, the next question would be noted here

This is the structure of the output of the webhook:
[
{
“responseId”: “…”,
“submissionId”: “…”,
“respondentId”: “…”,
“formId”: “…”,
“formName”: “…”,
“createdAt”: “…”,
“fields”: {…},
“eventId”: “…”,
“fieldsById”: {…}
}
]

The underlying issue is that I do not understand how to use Parse JSON or mapping correctly to retrieve the correct questions from these structures.

I also had some ideas about writing the questions into the Google sheet and later deleting some columns using the Google API, but it comes back to the question of mapping.

Another approach would be to retrieve the questions from a Google sheet, but that would need to be maintained by hand, which makes it prone to error, especially when it comes to making sure the values and the header match.

Thank you kindly

2

Hey Laurel,

Tally has a built in integration to save form responses directly to a google sheet. No need to use Make for this.

3

Thanks for your reply!

Since I need to sort the answers into different sheets depending on the answers given, Make still serves a purpose. However, your hint prompts me to attempt using the Google integration from Tally, read the answers from the Google sheet using Make and then sort the rows into different sheets depending on the rows using Make.

4

Thanks for your reply!

The Google Sheets integration works, however, Tally also sends the responses in the wide format containing the Booleans for each answer, leading back to the initial problem.

I managed to use Google Formulas to filter the columns containing booleans out:

=CHOOSECOLS(
  'All responses (from Tally webhook)'!A:BZ;
  FILTER(
    MTRANS(SEQUENCE(SPALTEN('All responses (from Tally webhook)'!A:BZ)));
    BYCOL(
      'All responses (from Tally webhook)'!A2:BZ;
      LAMBDA(col;
        SUMMENPRODUKT(--(ISTLOG(col)))=0
      )
    )
  )
)

I still need Make since the rows from the response collection need to be saved to different sheets depending on their values.

Using Make, I read the header row from that sheet, and handle it separated from the values since it’s needed to create new spreadsheets. I then handle the values by also reading them from that sheet, to then sort each row into the correct sheet depending on the value.

It’s not a pretty solution, but it works for now, and I didn’t find any better one so far.