Log every form submission to a sheet and post it to Slack

Every hour, the workflow reads your Typeform for submissions it has not handled yet, posts each one into a Slack channel so somebody sees it the same hour, and appends it to a spreadsheet as a permanent record. A backlog is worked through in batches over later runs.

Each Slack post shows when the submission arrived and its ID, then a line marking where the submitter's words begin, followed by their answers as one 'Question: answer' line per question in your form's wording. Anything a submitter typed is escaped so it cannot act as Slack formatting. The 'Response ID' column is how it knows what has been handled, so nothing is posted twice, and the message goes out before the row is written, so a run that fails halfway repeats one notification rather than losing a submission.

Point it at an empty tab and it writes the 'Submitted at', 'Response ID' and 'Answers' headings itself; a tab with headings but no 'Response ID' column makes it stop rather than repost everything.

Run every hour.Run every hour.

Set formId to blank.Set formId to blank.

Set spreadsheetLink to blank.Set spreadsheetLink to blank.

Set tabName to blank.Set tabName to blank.

Set slackChannelId to blank.Set slackChannelId to blank.

Set maxSubmissionsPerRun to 20.Set maxSubmissionsPerRun to 20.

TypeformList fields on Typeform form formId. Save it as formFields.List fields on Typeform form formId. Save it as formFields.

Set questions to formFields.fields.Set questions to formFields.fields.

Google SheetsRead the rows of spreadsheetLink. Save it as loggedSoFar.Read the rows of spreadsheetLink. Save it as loggedSoFar.

Set loggedHeaders to loggedSoFar.headers.Set loggedHeaders to loggedSoFar.headers.

If loggedHeaders is emptyIf loggedHeaders is empty

Yes

Google SheetsAdd a row to tabName in spreadsheetLink.Add a row to tabName in spreadsheetLink.

Else if @loggedHeaders doesn't contain "Response ID"

Stop and report a problem: The tab named in the 'tabName' step needs a column headed exactly 'Response ID'. Without it this workflow cannot tell which submissions it has already handled, and it would post every one of them again on every run. Point 'tabName' at an empty tab and it will write the headings itself.Stop and report a problem: The tab named in the 'tabName' step needs a column headed exactly 'Response ID'. Without it this workflow cannot tell which submissions it has already handled, and it would post every one of them again on every run. Point 'tabName' at an empty tab and it will write the headings itself.

Otherwise

No steps

Set seenIds to an empty list.Set seenIds to an empty list.

For each loggedRow in loggedSoFar.rows:For each loggedRow in loggedSoFar.rows:

Trim loggedRow["Response ID"] and save it as seenId.Trim loggedRow["Response ID"] and save it as seenId.

If seenId is nullIf seenId is null

Yes

Change seenId to blank.Change seenId to blank.

No

No steps

If seenId isn't emptyIf seenId isn't empty

Yes

Add seenId to the end of seenIds and save it as seenIds.Add seenId to the end of seenIds and save it as seenIds.

No

No steps

TypeformList responses to Typeform form formId. Save it as submissions.List responses to Typeform form formId. Save it as submissions.

Set loggedCount to 0.Set loggedCount to 0.

For each response in submissions.responses:For each response in submissions.responses:

If loggedCount multiplied by 1 is greater than or equal to maxSubmissionsPerRun…If loggedCount multiplied by 1 is greater than or equal to maxSubmissionsPerRun…

Yes

Stop repeating.Stop repeating.

No

No steps

Trim response.id and save it as responseId.Trim response.id and save it as responseId.

If responseId is nullIf responseId is null

Yes

Change responseId to blank.Change responseId to blank.

No

No steps

If responseId is emptyIf responseId is empty

Yes

Show in the output: Skipped a submission that arrived without a response id - there would be no way to tell it apart from the next one..Show in the output: Skipped a submission that arrived without a response id - there would be no way to tell it apart from the next one..

Skip to the next item.Skip to the next item.

No

No steps

If seenIds contains responseId is yesIf seenIds contains responseId is yes

Yes

Skip to the next item.Skip to the next item.

No

No steps

Trim response.submitted_at and save it as submittedAt.Trim response.submitted_at and save it as submittedAt.

If submittedAt is nullIf submittedAt is null

Yes

Change submittedAt to blank.Change submittedAt to blank.

No

No steps

Set answerLines to an empty list.Set answerLines to an empty list.

For each answer in response.answers:For each answer in response.answers:

Trim answer.field_id and save it as fieldId.Trim answer.field_id and save it as fieldId.

If fieldId is nullIf fieldId is null

Yes

Change fieldId to blank.Change fieldId to blank.

No

No steps

Trim answer.answer and save it as answerText.Trim answer.answer and save it as answerText.

If answerText is nullIf answerText is null

Yes

Change answerText to blank.Change answerText to blank.

No

No steps

Set questionTitle to blank.Set questionTitle to blank.

For each question in questions:For each question in questions:

Trim question.field_id and save it as questionFieldId.Trim question.field_id and save it as questionFieldId.

If questionFieldId is nullIf questionFieldId is null

Yes

Change questionFieldId to blank.Change questionFieldId to blank.

No

No steps

If fieldId isn't empty and questionFieldId is fieldIdIf fieldId isn't empty and questionFieldId is fieldId

Yes

Trim question.title and save it as questionTitle.Trim question.title and save it as questionTitle.

Stop repeating.Stop repeating.

No

No steps

If questionTitle is nullIf questionTitle is null

Yes

Change questionTitle to blank.Change questionTitle to blank.

No

No steps

If questionTitle is emptyIf questionTitle is empty

Yes

Change questionTitle to Answer.Change questionTitle to Answer.

No

No steps

Add questionTitle: answerText to the end of answerLines and save it as answerLines.Add questionTitle: answerText to the end of answerLines and save it as answerLines.

Join answerLines with a new line and save it as answersText.Join answerLines with a new line and save it as answersText.

If answersText is emptyIf answersText is empty

Yes

Change answersText to (this submission recorded no answers).Change answersText to (this submission recorded no answers).

No

No steps

Replace & with & in answersText and save it as escapedAmpersands.Replace & with & in answersText and save it as escapedAmpersands.

Replace < with < in escapedAmpersands and save it as escapedOpeners.Replace < with < in escapedAmpersands and save it as escapedOpeners.

Replace > with > in escapedOpeners and save it as escapedAnswers.Replace > with > in escapedOpeners and save it as escapedAnswers.

Shorten escapedAnswers to 2500 characters and save it as slackBody.Shorten escapedAnswers to 2500 characters and save it as slackBody.

Set slackHeader to New form submissionSet slackHeader to New form submission Submitted at: submittedAt Response id: responseId.

Set slackText to slackHeaderSet slackText to slackHeader --- everything below this line was typed by whoever filled in the form --- slackBody.

Shorten answersText to 20000 characters and save it as sheetAnswers.Shorten answersText to 20000 characters and save it as sheetAnswers.

If a step below fails, save the error as deliveryErrorIf a step below fails, save the error as deliveryError

Try

SlackSend a message to slackChannelId.Send a message to slackChannelId.slackText

Google SheetsAdd a row to tabName in spreadsheetLink.Add a row to tabName in spreadsheetLink.

Add 1 to loggedCount.Add 1 to loggedCount.

On error

Show in the output: Could not log submission responseId plus this time, so it stays unlogged and will be picked up on the next run: deliveryError.message.Show in the output: Could not log submission responseId plus this time, so it stays unlogged and will be picked up on the next run: deliveryError.message.

Show in the output: Logged loggedCount as text plus new form submissions to the '@tabName' tab and posted them to Slack.Show in the output: Logged loggedCount as text plus new form submissions to the '@tabName' tab and posted them to Slack.