Workflow: DSP Certificate w/ Google Forms
Integrations: gmail, googleDrive, googleSheetsTrigger, googleSlides, if, noOp, set, stickyNote

{"id": "2qIFnWXdHJJs4oBk", "meta": {"instanceId": "6c586999cefcd4ec9b2ab69e3f6b7974d96831b39a984af15104588e20b2737a", "templateCredsSetupCompleted": true}, "name": "DSP Certificate w/ Google Forms", "tags": [], "nodes": [{"id": "1f3a1bb2-1e5b-4696-aafc-5b3267d76cbf", "name": "Google Sheets Trigger", "type": "n8n-nodes-base.googleSheetsTrigger", "position": [-100, -20], "parameters": {"event": "rowAdded", "options": {}, "pollTimes": {"item": [{"mode": "everyMinute"}]}, "sheetName": {"__rl": true, "mode": "list", "value": 1715309269, "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1WqhSc4sx6GMupZgFo7xKoegXVo3fJVhqrovCQPa1esM/edit#gid=1715309269", "cachedResultName": "Form Responses 1"}, "documentId": {"__rl": true, "mode": "id", "value": "1WqhSc4sx6GMupZgFo7xKoegXVo3fJVhqrovCQPa1esM"}}, "credentials": {"googleSheetsTriggerOAuth2Api": {"id": "LPj2gg4OdDdyokS7", "name": "Google Sheets (jkp@kajonkietsuksa.ac.th)"}}, "typeVersion": 1}, {"id": "385f6b0f-2db0-4a44-816c-c6f6c8ccb493", "name": "No Operation, do nothing", "type": "n8n-nodes-base.noOp", "position": [620, 180], "parameters": {}, "typeVersion": 1}, {"id": "58a77733-99f1-4884-b955-0a6f6c983cfc", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [-240, -340], "parameters": {"width": 300, "height": 180, "content": "### 1) Start here\n* Create a Google Form and then enable quiz mode.\n* Publish it, submit 1 text data.\n* In response section, you'll see \"Link to Google Sheet\" option.\n* Press, and it will create a new sheet."}, "typeVersion": 1}, {"id": "aeef0ccc-3031-40d0-a627-5f21ade148b1", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [320, -140], "parameters": {"width": 180, "content": "### 4) Passing Score\n* Adjust your passing score here"}, "typeVersion": 1}, {"id": "c21dbdb5-ed87-4aac-bbc7-338aaed830ba", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [-240, -100], "parameters": {"height": 180, "content": "### 2) Trigger Node\n* Replace your Google Sheet id's in this node."}, "typeVersion": 1}, {"id": "d2b15c40-d38a-4bec-97c8-d4b35e3a69fa", "name": "Sticky Note3", "type": "n8n-nodes-base.stickyNote", "position": [40, -100], "parameters": {"width": 260, "height": 180, "content": "### 3) Extract Node\n* Select the data we want to use to proceed.\n* For this case, i'll select only Name, Email, Score (Because this is only what we need)"}, "typeVersion": 1}, {"id": "79957ca7-ac5f-4f5b-b921-ddec3cb9f88b", "name": "Extract essential data", "type": "n8n-nodes-base.set", "position": [120, 60], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "7cdc9108-ab77-4904-a74b-29677b06cc81", "name": "respondentName", "type": "string", "value": "={{ $json['\u0e0a\u0e37\u0e48\u0e2d (\u0e40\u0e1b\u0e47\u0e19\u0e20\u0e32\u0e29\u0e32\u0e2d\u0e31\u0e07\u0e01\u0e24\u0e29)'] }}"}, {"id": "1800b27a-6cbc-4b82-a17a-87d7d1e7a66e", "name": "respondentEmail", "type": "string", "value": "={{ $json['Email Address'] }}"}, {"id": "36cb99ca-7c98-41b5-a2a4-a03ac8d83189", "name": "respondentScore", "type": "number", "value": "={{ $json.Score }}"}]}}, "typeVersion": 3.4}, {"id": "912838e0-6b35-47a1-8935-dc90b4c59ecb", "name": "Score Checker", "type": "n8n-nodes-base.if", "position": [360, -20], "parameters": {"options": {}, "conditions": {"options": {"version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict"}, "combinator": "and", "conditions": [{"id": "286a95ee-1edc-4310-af22-d161e1f04a27", "operator": {"type": "number", "operation": "gt"}, "leftValue": "={{ $json.respondentScore }}", "rightValue": 3}]}}, "typeVersion": 2.2}, {"id": "9c9e308f-ce90-425d-aafc-08711cbf95df", "name": "Sticky Note4", "type": "n8n-nodes-base.stickyNote", "position": [600, 120], "parameters": {"width": 260, "content": "### 4.1) Score < passing criteria"}, "typeVersion": 1}, {"id": "f794c7a3-47af-4166-9504-8265837f61e6", "name": "Sticky Note5", "type": "n8n-nodes-base.stickyNote", "position": [520, -340], "parameters": {"width": 260, "height": 200, "content": "### 4.2) Score > passing criteria\n* Create new Google Slide \n* Decorate it as you desired (This will be certificate's template)\n* Use [ name ] to be a placeholder for user's name\n* Replace it with your Google Slide's id"}, "typeVersion": 1}, {"id": "9a2954e3-59fd-4472-931f-9eeb362e627b", "name": "Sticky Note6", "type": "n8n-nodes-base.stickyNote", "position": [820, -400], "parameters": {"width": 260, "content": "### 5) Replace text\n* This node will replace [ name ] with user's input name.\n"}, "typeVersion": 1}, {"id": "baa88ba8-c1c6-40d7-b4c0-1e70397d7e68", "name": "Sticky Note7", "type": "n8n-nodes-base.stickyNote", "position": [940, -80], "parameters": {"width": 260, "content": "### 6) To PDF\n* Change file name as you desire."}, "typeVersion": 1}, {"id": "0d4b0fad-046b-4810-9d21-2c30135df6b0", "name": "Copy from your template", "type": "n8n-nodes-base.googleDrive", "position": [620, -160], "parameters": {"name": "={{ $json.respondentName }}'s Certificate", "fileId": {"__rl": true, "mode": "id", "value": "1J8PxjjspVs7075EfIX6pnNU-TmqtzVV9ymeHoKpbwP0"}, "driveId": {"__rl": true, "mode": "list", "value": "My Drive"}, "options": {}, "folderId": {"__rl": true, "mode": "list", "value": "1xMJU-6eiXL53NDgjic2SXecTo6GeUJ-o", "cachedResultUrl": "https://drive.google.com/drive/folders/1xMJU-6eiXL53NDgjic2SXecTo6GeUJ-o", "cachedResultName": "KS Google Form -> Certificate System"}, "operation": "copy", "sameFolder": false}, "credentials": {"googleDriveOAuth2Api": {"id": "2k4spLmVESgxckkx", "name": "jkp@kajonkietsuksa.ac.th"}}, "typeVersion": 3}, {"id": "30407819-7998-4ba1-b2a0-bde7ba91747c", "name": "Replace text", "type": "n8n-nodes-base.googleSlides", "position": [880, -300], "parameters": {"textUi": {"textValues": [{"text": "[ NAME ]", "replaceText": "={{ $('Score Checker').item.json.respondentName }}", "pageObjectIds": ["p"]}]}, "options": {}, "operation": "replaceText", "presentationId": "={{ $json.id }}"}, "credentials": {"googleSlidesOAuth2Api": {"id": "1oyCPsdPLod92Wlp", "name": "Google Slides account"}}, "typeVersion": 2}, {"id": "62f1ab2e-0471-480b-9a90-587a9ffb18d6", "name": "Convert to PDF", "type": "n8n-nodes-base.googleDrive", "position": [960, 0], "parameters": {"fileId": {"__rl": true, "mode": "id", "value": "={{ $json.presentationId }}"}, "options": {"fileName": "={{ $('Score Checker').item.json.respondentName }}'s Certificate", "googleFileConversion": {"conversion": {"slidesToFormat": "application/pdf"}}}, "operation": "download"}, "credentials": {"googleDriveOAuth2Api": {"id": "2k4spLmVESgxckkx", "name": "jkp@kajonkietsuksa.ac.th"}}, "typeVersion": 3, "alwaysOutputData": false}, {"id": "08516c84-5257-4875-8c2f-9b6a4428bfad", "name": "Send to user's email", "type": "n8n-nodes-base.gmail", "position": [1360, 0], "webhookId": "f204ef80-937c-4f7b-8eb5-0699eb13c16a", "parameters": {"sendTo": "={{ $('Score Checker').item.json.respondentEmail }}", "message": "=Congratulations on passing the quiz! Attached is your certificate.", "options": {"attachmentsUi": {"attachmentsBinary": [{}]}, "appendAttribution": false}, "subject": "Here's your certificate!!"}, "credentials": {"gmailOAuth2": {"id": "qogKxJFIxmrd6rcB", "name": "Gmail account (jkp@kajonkietsuksa.ac.th)"}}, "typeVersion": 2.1}, {"id": "ae4cd0de-e06d-4200-af17-f6e9953ccba7", "name": "Sticky Note8", "type": "n8n-nodes-base.stickyNote", "position": [1260, -100], "parameters": {"width": 260, "content": "### 7) Send email\n* Send to user's email\n* Customize your message here.\n"}, "typeVersion": 1}], "active": true, "pinData": {}, "settings": {"executionOrder": "v1"}, "versionId": "54bf009a-3f95-446d-95a6-825496592a6f", "connections": {"Replace text": {"main": [[{"node": "Convert to PDF", "type": "main", "index": 0}]]}, "Score Checker": {"main": [[{"node": "Copy from your template", "type": "main", "index": 0}], [{"node": "No Operation, do nothing", "type": "main", "index": 0}]]}, "Convert to PDF": {"main": [[{"node": "Send to user's email", "type": "main", "index": 0}]]}, "Google Sheets Trigger": {"main": [[{"node": "Extract essential data", "type": "main", "index": 0}]]}, "Extract essential data": {"main": [[{"node": "Score Checker", "type": "main", "index": 0}]]}, "Copy from your template": {"main": [[{"node": "Replace text", "type": "main", "index": 0}]]}}}