Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

4geru sakisaka
December 05, 2022
37

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

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

4geru sakisaka

December 05, 2022
Tweet

Transcript

  1. Google Form ͱͪΐͬͱ


    ஥ྑ͘ͳͬͨ࿩ɻ
    2022-12-05


    Google Apps Script LTձʂ
    @͛͠Δ

    View full-size slide

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

    View full-size slide

  3. ͋ͷύϯμ🐼


    Ͱ֮͑ͯͶʂ

    View full-size slide

  4. Ξϯέʔτͷ


    ࣗಈੜ੒ͨ͘͠ͳ͍ʁ

    View full-size slide

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

    View full-size slide

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

    ɹhttps://moneyforward.com/engineers_blog/2022/12/01/lgtm-award/

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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


    ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞

    View full-size slide

  10. ࣗಈੜ੒


    Έͨ͘ͳ͍ʁ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  13. Google Form API


    ஌Γ͍ͨͰ͢ΑͶ

    View full-size slide

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


    View full-size slide

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

    View full-size slide

  16. αϯϓϧίʔυ


    Έ͍ͨͰ͢ΑͶʁ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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


    View full-size slide

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

    View full-size slide

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

    View full-size slide

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


    બ୒ࢶͷදࣔ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  31. αϯϓϧίʔυ


    ؆୯͡Όͳ͍ʁ

    View full-size slide

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

    View full-size slide

  33. GAS͸ิ׬͕࠷ڧʂ

    View full-size slide

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

    View full-size slide

  35. ݴޠબ୒ϖʔδ
    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()


    }


    }


    νΣοΫϘοΫεͷ


    αϯϓϧΛҾ༻͠


    ݴޠબ୒ͷग़͠Θ͚


    Λ͠·͢


    ϖʔδભҠ ͸બ୒ઌ


    ͷؔ਺Ͱࢦఆ͠·͢ɻ

    View full-size slide

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

    View full-size slide

  37. ೔ຊޠϖʔδ
    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


    ͠ɺΧςΰϦʔ͝ͱʹ


    બ୒ࢶΛग़͍ͯ͠·͢

    View full-size slide

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

    View full-size slide

  39. ӳޠϖʔδ
    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Ͱ຋༁


    Λ͍ͯ͠·͢ɻ


    ※ ແྉϓϥϯͰ͢

    View full-size slide

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


    ίίʂҎ֎͸ zenn Λݟ͍ͯͩ͘͞

    View full-size slide

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

    View full-size slide

  42. Google Form API


    ࢖͍͍ͨͰ͢ΑͶ

    View full-size slide