Google Form とちょっと 仲良くなった話。

Google Apps Script LT会！ で登壇した内容です。
https://yumenosora.connpass.com/event/265341/

Transcript

1. Google Form ͱͪΐͬͱ ஥ྑ͘ͳͬͨ࿩ɻ 2022-12-05 Google Apps Script LTձʂ @͛͠Δ
2. ࣗݾ঺հ Name: ͛͠Δ Twitter: @_4geru Github, Zenn: @4geru Primary Language: Ruby♥♥ Interest: QA, Flutter Work: Money Forward Live: Tokyo, From: Shiga Hobby: Diving 🤿, Camera 📸, Aquarium 🐟 Current News: ΠϯϑϧΤϯβͷϫΫν ϯଧͪ·ͨ͠
3. ͋ͷύϯμ🐼 Ͱ֮͑ͯͶʂ
4. Ξϯέʔτͷ ࣗಈੜ੒ͨ͘͠ͳ͍ʁ
5. LGTM৆ʹ͍ͭͯ ϚωʔϑΥϫʔυ͸Valueͷ̍ͭʹɺ ςΫϊϩδʔυϦϒϯ Λܝ͍͛ͯ·͢ɻ ςΫϊ ϩδʔυϦϒϯ ͕ৗʹମݱ͞ΕɺจԽͱͯࠜ͠෇͍͍ͯΔձࣾΛ໨ࢦ͍ͯ͠·͢ɻ ձ ࣾͷจԽ͸ɺ೔ʑͷ׆ಈͷ݁Ռৢ੒͞Εͯ͘Δ΋ͷͩͱࢥ͍·͢͠ɺ ςΫϊϩδʔυϦ ϒϯͳจԽ Λ࡞ΕΔ͔Ͳ͏͔͸ɺ೔ʑͷΤϯδχΞͷ׆ಈͷ݁Ռ࣍ୈͩͱࢥ͍·͢ɻ LGTM৆Λ΍ͬͯΈΑ͏ͱࢥͬͨͷ͸ɺςΫϊϩδʔυϦϒϯ ͩͱײ͡Δ׆ಈΛΈΜͳ ʹਪનͯ͠΋Β͏͜ͱͰɺMFͷΤϯδχΞ͕ཧ૝ͱ͢Δ ςΫϊϩδʔυϦϒϯ ͷղ૾ ౓Λ্͛ɺจԽͱͯࠜ͠෇ͨ͘Ίͷ଍͕͔Γʹͯ͠΄͍͠ɺͱ͍͏ࢥ͍͔ΒͰ͢ɻ
6. LGTM৆ʹ͍ͭͯ • ස౓͸݄ʹ1ɺࣾһશһΛର৅ʹެื͍ͯ͠·͢ɻ • LGTM৆ʹ͍ͭͯ͸ΞυϕϯτΧϨϯμʔʹ΋͋ΔͷͰɺݟͯΈ͍ͯͩ͘͞ φϨοδͷཎΈΛղফ͢ΔLGTM৆ ʙখ࢝͘͞Ίͯେ͖͘ҭͯΔίϛϡχςΟϚωδϝϯτʙɿ
 ɹhttps://moneyforward.com/engineers_blog/2022/12/01/lgtm-award/
7. LGTM৆ͷӡ༻ʹ͍ͭͯ(before) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • ٕज़޿ใ͕ Google Form ʹసه • Թ͔Έͷ͋Δӡ༻Ͱɺຖճ͕͔͔͍࣌ؒͬͯ·ͨ͠ɻ • ίϐϖͷϛε΋ͳ͍͔֬ೝ͢Δ࡞ۀ΋ൃੜ • ਓͷख࡞ۀ
8. LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ • ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠
9. LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ • ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞
10. ࣗಈੜ੒ Έͨ͘ͳ͍ʁ
12. ͍͢͝ʂ
13. ϖʔδʹ͍ͭͯ ※ ݱࡏͷӡ༻Ͱ͸ɺݴޠબ୒͸ͳ͘ͳΓಉҰFormʹදࣔ͞Ε͍ͯ·͢ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ
14. ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ
15. γϯϓϧʂ
16. Google Form API ஌Γ͍ͨͰ͢ΑͶ
17. https://developers.google.com/apps-script/reference/forms αϯϓϧίʔυ͕ॆ࣮͍ͯ͠Δʂ // Create a new form, then add a checkbox question, a multiple choice question, // a page break, then a date question and a grid of questions. var form = FormApp.create('New Form'); var item = form.addCheckboxItem(); item.setTitle('What condiments would you like on your hot dog?'); item.setChoices([ item.createChoice('Ketchup'), item.createChoice('Mustard'), item.createChoice('Relish') ]); form.addMultipleChoiceItem() .setTitle('Do you prefer cats or dogs?') .setChoiceValues(['Cats','Dogs']) .showOtherOption(true); form.addPageBreakItem() .setTitle('Getting to know you'); form.addDateItem() .setTitle('When were you born?'); form.addGridItem() .setTitle('Rate your interests') .setRows(['Cars', 'Computers', 'Celebrities']) .setColumns(['Boring', 'So-so', 'Interesting']); Logger.log('Published URL: ' + form.getPublishedUrl()); Logger.log('Editor URL: ' + form.getEditUrl());
18. ࢖͍ͬͯΔؔ਺ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ 1. ϖʔδભҠ 2. ϥδΦϘλϯ 3. νΣοΫϘοΫε 3. νΣοΫϘοΫε 4.ஈམ 4.ஈམ 3. νΣοΫϘοΫε 3. νΣοΫϘοΫε
19. αϯϓϧίʔυ Έ͍ͨͰ͢ΑͶʁ
20. https://developers.google.com/apps-script/reference/forms/page-break-item Sample code1. ϖʔδભҠ // Create a form and add three page-break items. var form = FormApp.create('Form Name'); var pageTwo = form.addPageBreakItem().setTitle('Page Two'); var pageThree = form.addPageBreakItem().setTitle('Page Three'); // Make the first two pages navigate elsewhere upon completion. // At end of page one (start of page two), jump to page three pageTwo.setGoToPage(pageThree); // At end of page two, restart form pageThree.setGoToPage(FormApp.PageNavigationType.RESTART);
21. https://developers.google.com/apps-script/reference/forms/page-break-item Sample code1. ϖʔδભҠ // Create a form and add three page-break items. var form = FormApp.create('Form Name'); var pageTwo = form.addPageBreakItem().setTitle('Page Two'); var pageThree = form.addPageBreakItem().setTitle('Page Three'); // Make the first two pages navigate elsewhere upon completion. // At end of page one (start of page two), jump to page three pageTwo.setGoToPage(pageThree); // At end of page two, restart form pageThree.setGoToPage(FormApp.PageNavigationType.RESTART); Form ͷ࡞੒
22. https://developers.google.com/apps-script/reference/forms/page-break-item Sample code1. ϖʔδભҠ // Create a form and add three page-break items. var form = FormApp.create('Form Name'); var pageTwo = form.addPageBreakItem().setTitle('Page Two'); var pageThree = form.addPageBreakItem().setTitle('Page Three'); // Make the first two pages navigate elsewhere upon completion. // At end of page one (start of page two), jump to page three pageTwo.setGoToPage(pageThree); // At end of page two, restart form pageThree.setGoToPage(FormApp.PageNavigationType.RESTART); ϖʔδͷࢦఆ ϖʔδ΁ͷϦϯΫઃఆ
23. https://developers.google.com/apps-script/reference/forms/checkbox-item Sample code 2. ϥδΦϘλϯ // Open a form by ID and add a new checkbox item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addCheckboxItem(); item.setTitle('What condiments would you like on your hot dog?') .setChoices([ item.createChoice('Ketchup'), item.createChoice('Mustard'), item.createChoice('Relish') ]) .showOtherOption(true);
24. https://developers.google.com/apps-script/reference/forms/checkbox-item Sample code 2. ϥδΦϘλϯ // Open a form by ID and add a new checkbox item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addCheckboxItem(); item.setTitle('What condiments would you like on your hot dog?') .setChoices([ item.createChoice('Ketchup'), item.createChoice('Mustard'), item.createChoice('Relish') ]) .showOtherOption(true); Form ͷಡΈࠐΈ
25. https://developers.google.com/apps-script/reference/forms/checkbox-item Sample code 2. ϥδΦϘλϯ // Open a form by ID and add a new checkbox item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addCheckboxItem(); item.setTitle('What condiments would you like on your hot dog?') .setChoices([ item.createChoice('Ketchup'), item.createChoice('Mustard'), item.createChoice('Relish') ]) .showOtherOption(true); Item ͷઃఆ
26. https://developers.google.com/apps-script/reference/forms/checkbox-item Sample code 2. ϥδΦϘλϯ // Open a form by ID and add a new checkbox item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addCheckboxItem(); item.setTitle('What condiments would you like on your hot dog?') .setChoices([ item.createChoice('Ketchup'), item.createChoice('Mustard'), item.createChoice('Relish') ]) .showOtherOption(true); બ୒ࢶͷදࣔ
27. https://developers.google.com/apps-script/reference/forms/multiple-choice-item Sample code 3. νΣοΫϘοΫε // Open a form by ID and add a new multiple choice item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addMultipleChoiceItem(); item.setTitle('Do you prefer cats or dogs?') .setChoices([ item.createChoice('Cats'), item.createChoice('Dogs') ]) .showOtherOption(true);
28. https://developers.google.com/apps-script/reference/forms/multiple-choice-item Sample code 3. νΣοΫϘοΫε // Open a form by ID and add a new multiple choice item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addMultipleChoiceItem(); item.setTitle('Do you prefer cats or dogs?') .setChoices([ item.createChoice('Cats'), item.createChoice('Dogs') ]) .showOtherOption(true); Form ͷಡΈࠐΈ
29. https://developers.google.com/apps-script/reference/forms/multiple-choice-item Sample code 3. νΣοΫϘοΫε // Open a form by ID and add a new multiple choice item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addMultipleChoiceItem(); item.setTitle('Do you prefer cats or dogs?') .setChoices([ item.createChoice('Cats'), item.createChoice('Dogs') ]) .showOtherOption(true); Item ͷઃఆ
30. https://developers.google.com/apps-script/reference/forms/multiple-choice-item Sample code 3. νΣοΫϘοΫε // Open a form by ID and add a new multiple choice item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addMultipleChoiceItem(); item.setTitle('Do you prefer cats or dogs?') .setChoices([ item.createChoice('Cats'), item.createChoice('Dogs') ]) .showOtherOption(true); બ୒ࢶͷදࣔ
31. https://developers.google.com/apps-script/reference/forms/paragraph-text-item Sample code 4. ஈམ // Open a form by ID and add a new paragraph text item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addParagraphTextItem(); item.setTitle('What is your address?');
32. https://developers.google.com/apps-script/reference/forms/paragraph-text-item Sample code 4. ஈམ // Open a form by ID and add a new paragraph text item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addParagraphTextItem(); item.setTitle('What is your address?'); Form ͷಡΈࠐΈ
33. https://developers.google.com/apps-script/reference/forms/paragraph-text-item Sample code 4. ஈམ // Open a form by ID and add a new paragraph text item. var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); var item = form.addParagraphTextItem(); item.setTitle('What is your address?'); Item ͷઃఆ
34. αϯϓϧίʔυ ؆୯͡Όͳ͍ʁ
35. େମͷߏจΛ஌͍ͬͯΕ͹ɺิ׬ͯ͘͠ΕΔ GAS ͸ා͘ͳ͍
36. GAS͸ิ׬͕࠷ڧʂ
37. ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ
38. ݴޠબ୒ϖʔδ const separatedExportGooleForm = () => { const form = FormApp.create(separatedGoogleFormTitle()) .setDescription(formDescription); let item = form.addMultipleChoiceItem(); item.setTitle('ݴޠΛબ୒͍ͯͩ͘͠͞ɻ / Please select your language.') item.setChoices([ item.createChoice('೔ຊޠ / Japanese', separatedJapaneseForm(form)), item.createChoice('ӳޠ / English', separatedEnglishForm(form)), ]); return { publishedUrl: form.getPublishedUrl(), editUrl: form.getEditUrl() } } νΣοΫϘοΫεͷ αϯϓϧΛҾ༻͠ ݴޠબ୒ͷग़͠Θ͚ Λ͠·͢ ϖʔδભҠ ͸બ୒ઌ ͷؔ਺Ͱࢦఆ͠·͢ɻ
39. ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ
40. ೔ຊޠϖʔδ const separatedJapaneseForm = (form) => { let japanesePage = form.addPageBreakItem().setTitle('೔ຊޠ'); const spreadSheetHash = separatedFetchSpreadSheetHash(); for (let key in spreadSheetHash) { let item = form.addCheckboxItem(); item.setTitle(key); const choices = spreadSheetHash[key].map((row) => { return item.createChoice(row[1] + row[2]) }) item.setChoices(choices); } form.addParagraphTextItem() .setTitle('ྭ·͠ɾਪનϝοηʔδɾͦͷଞ͝ҙݟײ૝ͳͲͳΜͰ΋ࣗ༝هೖཝ') japanesePage.setGoToPage(FormApp.PageNavigationType.SUBMIT) return japanesePage } νΣοΫϘοΫε ஈམɺϖʔδભҠͷ αϯϓϧΛҾ༻͠·͢ Spread Sheets ͔ΒFetch ͠ɺΧςΰϦʔ͝ͱʹ બ୒ࢶΛग़͍ͯ͠·͢
41. ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ
42. ӳޠϖʔδ const separatedEnglishForm = (form) => { let englishPage = form.addPageBreakItem().setTitle('English'); const spreadSheetHash = separatedFetchSpreadSheetHash(); for (let key in spreadSheetHash) { let item = form.addCheckboxItem(); item.setTitle(deeplTranslate([key], "JA", "EN")[0].text); const choices = spreadSheetHash[key].map((row) => { const choice = deeplTranslate([row[1] + row[2]], "JA", "EN")[0].text return item.createChoice(choice) }) item.setChoices(choices); } form.addParagraphTextItem() .setTitle('Encouragement, recommendation messages…’) englishPage.setGoToPage(FormApp.PageNavigationType.SUBMIT) return englishPage } ߏ੒͸೔ຊޠϖʔδͱ ಉ͡ͰɺDeepLͰ຋༁ Λ͍ͯ͠·͢ɻ ※ ແྉϓϥϯͰ͢
43. LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ • ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞
44. ϚωϑΥΞυϕϯτΧϨϯμʔ2022 4೔໨ ʹผͷAPI΋·ͱΊ͍ͯ·͢ɻ ίϐϖ͢Δ͜ͱͰ؆୯ʹಈ͘ͷͰɺͥͻʂ͓खݩͰ΋ࢼ͍ͩ͘͠͞ɻ • Google Sheets • Kibela(GraphQL) • DeepL(RestAPI) • Google Form API Google Apps Script(GAS)Λ࢖͍౗ͯ͠ɺΞϯέʔτ࡞੒ΛࣗಈԽͨ͠࿩ https://zenn.dev/4geru/books/advent-calendar-2022-day-4 Zenn ʹ͍ͭͯ
45. Google Form API ࢖͍͍ͨͰ͢ΑͶ