Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

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

4geru sakisaka
December 05, 2022
53

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

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

4geru sakisaka

December 05, 2022
Tweet

Transcript

  1. ࣗݾ঺հ 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: ΠϯϑϧΤϯβͷϫΫν ϯଧͪ·ͨ͠
  2. LGTM৆ʹ͍ͭͯ ϚωʔϑΥϫʔυ͸Valueͷ̍ͭʹɺ ςΫϊϩδʔυϦϒϯ Λܝ͍͛ͯ·͢ɻ ςΫϊ ϩδʔυϦϒϯ ͕ৗʹମݱ͞ΕɺจԽͱͯࠜ͠෇͍͍ͯΔձࣾΛ໨ࢦ͍ͯ͠·͢ɻ ձ ࣾͷจԽ͸ɺ೔ʑͷ׆ಈͷ݁Ռৢ੒͞Εͯ͘Δ΋ͷͩͱࢥ͍·͢͠ɺ ςΫϊϩδʔυϦ

    ϒϯͳจԽ Λ࡞ΕΔ͔Ͳ͏͔͸ɺ೔ʑͷΤϯδχΞͷ׆ಈͷ݁Ռ࣍ୈͩͱࢥ͍·͢ɻ LGTM৆Λ΍ͬͯΈΑ͏ͱࢥͬͨͷ͸ɺςΫϊϩδʔυϦϒϯ ͩͱײ͡Δ׆ಈΛΈΜͳ ʹਪનͯ͠΋Β͏͜ͱͰɺMFͷΤϯδχΞ͕ཧ૝ͱ͢Δ ςΫϊϩδʔυϦϒϯ ͷղ૾ ౓Λ্͛ɺจԽͱͯࠜ͠෇ͨ͘Ίͷ଍͕͔Γʹͯ͠΄͍͠ɺͱ͍͏ࢥ͍͔ΒͰ͢ɻ
  3. LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets

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

    Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ • ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞
  5. 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());
  6. ࢖͍ͬͯΔؔ਺ ݴޠબ୒ϖʔδ ೔ຊޠϖʔδ ӳޠϖʔδ 1. ϖʔδભҠ 2. ϥδΦϘλϯ 3. νΣοΫϘοΫε

    3. νΣοΫϘοΫε 4.ஈམ 4.ஈམ 3. νΣοΫϘοΫε 3. νΣοΫϘοΫε
  7. 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);
  8. 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 ͷ࡞੒
  9. 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); ϖʔδͷࢦఆ ϖʔδ΁ͷϦϯΫઃఆ
  10. 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);
  11. 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 ͷಡΈࠐΈ
  12. 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 ͷઃఆ
  13. 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); બ୒ࢶͷදࣔ
  14. 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);
  15. 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 ͷಡΈࠐΈ
  16. 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 ͷઃఆ
  17. 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); બ୒ࢶͷදࣔ
  18. 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?');
  19. 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 ͷಡΈࠐΈ
  20. 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 ͷઃఆ
  21. ݴޠબ୒ϖʔδ 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() } } νΣοΫϘοΫεͷ αϯϓϧΛҾ༻͠ ݴޠબ୒ͷग़͠Θ͚ Λ͠·͢ ϖʔδભҠ ͸બ୒ઌ ͷؔ਺Ͱࢦఆ͠·͢ɻ
  22. ೔ຊޠϖʔδ 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 ͠ɺΧςΰϦʔ͝ͱʹ બ୒ࢶΛग़͍ͯ͠·͢
  23. ӳޠϖʔδ 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Ͱ຋༁ Λ͍ͯ͠·͢ɻ ※ ແྉϓϥϯͰ͢
  24. LGTM৆ͷӡ༻ʹ͍ͭͯ(after) • υΩϡϝϯτπʔϧʹՕ৚ॻ͖Ͱืू • Google Sheets ʹΠϯϙʔτ • Google Sheets

    Ͱจݴमਖ਼ • Google Form ʹΤΫεϙʔτ • DeepL Λ࢖ͬͯӳޠʹಉ࣌຋༁ • ਓͷख࡞ۀ after Ͱ͸ɺసه࡞ۀ͕ͳ͘ͳΓɺ֬ೝ࡞ۀΛ͢Δ͚ͩ + ӳޠͷ຋༁΋͖ͭ·ͨ͠ ࠓճ࿩͢ͷ͸ίίʂ ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞
  25. ϚωϑΥΞυϕϯτΧϨϯμʔ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 ʹ͍ͭͯ