Slide 1

Slide 1 text

Google Form ͱͪΐͬͱ ஥ྑ͘ͳͬͨ࿩ɻ 2022-12-05 Google Apps Script LTձʂ @͛͠Δ

Slide 2

Slide 2 text

ࣗݾ঺հ 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: ΠϯϑϧΤϯβͷϫΫν ϯଧͪ·ͨ͠

Slide 3

Slide 3 text

͋ͷύϯμ🐼 Ͱ֮͑ͯͶʂ

Slide 4

Slide 4 text

Ξϯέʔτͷ ࣗಈੜ੒ͨ͘͠ͳ͍ʁ

Slide 5

Slide 5 text

LGTM৆ʹ͍ͭͯ ϚωʔϑΥϫʔυ͸Valueͷ̍ͭʹɺ ςΫϊϩδʔυϦϒϯ Λܝ͍͛ͯ·͢ɻ ςΫϊ ϩδʔυϦϒϯ ͕ৗʹମݱ͞ΕɺจԽͱͯࠜ͠෇͍͍ͯΔձࣾΛ໨ࢦ͍ͯ͠·͢ɻ ձ ࣾͷจԽ͸ɺ೔ʑͷ׆ಈͷ݁Ռৢ੒͞Εͯ͘Δ΋ͷͩͱࢥ͍·͢͠ɺ ςΫϊϩδʔυϦ ϒϯͳจԽ Λ࡞ΕΔ͔Ͳ͏͔͸ɺ೔ʑͷΤϯδχΞͷ׆ಈͷ݁Ռ࣍ୈͩͱࢥ͍·͢ɻ LGTM৆Λ΍ͬͯΈΑ͏ͱࢥͬͨͷ͸ɺςΫϊϩδʔυϦϒϯ ͩͱײ͡Δ׆ಈΛΈΜͳ ʹਪનͯ͠΋Β͏͜ͱͰɺMFͷΤϯδχΞ͕ཧ૝ͱ͢Δ ςΫϊϩδʔυϦϒϯ ͷղ૾ ౓Λ্͛ɺจԽͱͯࠜ͠෇ͨ͘Ίͷ଍͕͔Γʹͯ͠΄͍͠ɺͱ͍͏ࢥ͍͔ΒͰ͢ɻ

Slide 6

Slide 6 text

LGTM৆ʹ͍ͭͯ • ස౓͸݄ʹ1ɺࣾһશһΛର৅ʹެื͍ͯ͠·͢ɻ • LGTM৆ʹ͍ͭͯ͸ΞυϕϯτΧϨϯμʔʹ΋͋ΔͷͰɺݟͯΈ͍ͯͩ͘͞ φϨοδͷཎΈΛղফ͢ΔLGTM৆ ʙখ࢝͘͞Ίͯେ͖͘ҭͯΔίϛϡχςΟϚωδϝϯτʙɿ
 ɹhttps://moneyforward.com/engineers_blog/2022/12/01/lgtm-award/

Slide 7

Slide 7 text

LGTM৆ͷӡ༻ʹ͍ͭͯ(before) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • ٕज़޿ใ͕ Google Form ʹసه • Թ͔Έͷ͋Δӡ༻Ͱɺຖճ͕͔͔͍࣌ؒͬͯ·ͨ͠ɻ • ίϐϖͷϛε΋ͳ͍͔֬ೝ͢Δ࡞ۀ΋ൃੜ ● ਓͷख࡞ۀ

Slide 8

Slide 8 text

LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ ● ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠

Slide 9

Slide 9 text

LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ ● ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞

Slide 10

Slide 10 text

ࣗಈੜ੒ Έͨ͘ͳ͍ʁ

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

͍͢͝ʂ

Slide 13

Slide 13 text

ϖʔδʹ͍ͭͯ ※ ݱࡏͷӡ༻Ͱ͸ɺݴޠબ୒͸ͳ͘ͳΓಉҰFormʹදࣔ͞Ε͍ͯ·͢ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ

Slide 14

Slide 14 text

ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ

Slide 15

Slide 15 text

γϯϓϧʂ

Slide 16

Slide 16 text

Google Form API ஌Γ͍ͨͰ͢ΑͶ

Slide 17

Slide 17 text

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());

Slide 18

Slide 18 text

࢖͍ͬͯΔؔ਺ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ 1. ϖʔδભҠ 2. ϥδΦϘλϯ 3. νΣοΫϘοΫε 3. νΣοΫϘοΫε 4.ஈམ 4.ஈམ 3. νΣοΫϘοΫε 3. νΣοΫϘοΫε

Slide 19

Slide 19 text

αϯϓϧίʔυ Έ͍ͨͰ͢ΑͶʁ

Slide 20

Slide 20 text

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);

Slide 21

Slide 21 text

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 ͷ࡞੒

Slide 22

Slide 22 text

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); ϖʔδͷࢦఆ ϖʔδ΁ͷϦϯΫઃఆ

Slide 23

Slide 23 text

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);

Slide 24

Slide 24 text

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 ͷಡΈࠐΈ

Slide 25

Slide 25 text

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 ͷઃఆ

Slide 26

Slide 26 text

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); બ୒ࢶͷදࣔ

Slide 27

Slide 27 text

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);

Slide 28

Slide 28 text

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 ͷಡΈࠐΈ

Slide 29

Slide 29 text

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 ͷઃఆ

Slide 30

Slide 30 text

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); બ୒ࢶͷදࣔ

Slide 31

Slide 31 text

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?');

Slide 32

Slide 32 text

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 ͷಡΈࠐΈ

Slide 33

Slide 33 text

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 ͷઃఆ

Slide 34

Slide 34 text

αϯϓϧίʔυ ؆୯͡Όͳ͍ʁ

Slide 35

Slide 35 text

େମͷߏจΛ஌͍ͬͯΕ͹ɺิ׬ͯ͘͠ΕΔ GAS ͸ා͘ͳ͍

Slide 36

Slide 36 text

GAS͸ิ׬͕࠷ڧʂ

Slide 37

Slide 37 text

ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ

Slide 38

Slide 38 text

ݴޠબ୒ϖʔδ 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() } } νΣοΫϘοΫεͷ αϯϓϧΛҾ༻͠ ݴޠબ୒ͷग़͠Θ͚ Λ͠·͢ ϖʔδભҠ ͸બ୒ઌ ͷؔ਺Ͱࢦఆ͠·͢ɻ

Slide 39

Slide 39 text

ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ

Slide 40

Slide 40 text

೔ຊޠϖʔδ 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 ͠ɺΧςΰϦʔ͝ͱʹ બ୒ࢶΛग़͍ͯ͠·͢

Slide 41

Slide 41 text

ϖʔδʹ͍ͭͯ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ

Slide 42

Slide 42 text

ӳޠϖʔδ 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Ͱ຋༁ Λ͍ͯ͠·͢ɻ ※ ແྉϓϥϯͰ͢

Slide 43

Slide 43 text

LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ ● ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞

Slide 44

Slide 44 text

ϚωϑΥΞυϕϯτΧϨϯμʔ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 ʹ͍ͭͯ

Slide 45

Slide 45 text

Google Form API ࢖͍͍ͨͰ͢ΑͶ