{"id":67338,"date":"2024-07-05T14:30:30","date_gmt":"2024-07-05T05:30:30","guid":{"rendered":"https:\/\/support.questetra.com\/?p=67338"},"modified":"2025-07-18T11:58:41","modified_gmt":"2025-07-18T02:58:41","slug":"googlesheets-getidslabels","status":"publish","type":"post","link":"https:\/\/support.questetra.com\/en\/bpmn-icons\/googlesheets-getidslabels\/","title":{"rendered":"Google Sheets: Download Choice Data"},"content":{"rendered":"\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"58\" data-attachment-id=\"113160\" data-permalink=\"https:\/\/support.questetra.com\/en\/developer-blog\/how-to-use-timer-start-event\/attachment\/advanced-banner-new-en\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?fit=1200%2C68&amp;ssl=1\" data-orig-size=\"1200,68\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"advanced-banner-new-en\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?fit=1024%2C58&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en-1024x58.png?resize=1024%2C58&#038;ssl=1\" alt=\"\" class=\"wp-image-113160\" srcset=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?resize=1024%2C58&amp;ssl=1 1024w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?resize=600%2C34&amp;ssl=1 600w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?resize=768%2C44&amp;ssl=1 768w, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/05\/advanced-banner-new-en.png?w=1200&amp;ssl=1 1200w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h4 class=\"wp-block-heading has-border-color has-product-main-border-color has-white-color has-product-main-background-color has-text-color has-background has-link-color\" style=\"border-width:2px;margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;padding-top:var(--wp--preset--spacing--10);padding-right:var(--wp--preset--spacing--20);padding-bottom:var(--wp--preset--spacing--10);padding-left:var(--wp--preset--spacing--20)\"><strong>Google Sheets: Download Choice Data<\/strong><\/h4>\n\n\n\n<p class=\"has-text-align-right has-border-color has-product-main-border-color has-white-color has-product-main-background-color has-text-color has-background has-link-color has-small-font-size mt-0 pt-0 wp-block-paragraph\" style=\"border-width:2px;margin-top:0;margin-right:0;margin-bottom:0;margin-left:0;padding-top:0;padding-right:var(--wp--preset--spacing--20);padding-bottom:0;padding-left:var(--wp--preset--spacing--20)\"><a href=\"https:\/\/support.questetra.com\/en\/bpmn-icons\/googlesheets-getidslabels\/\">Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8: \u9078\u629e\u80a2\u30c7\u30fc\u30bf\u306e\u4e00\u62ec\u53d6\u5f97<\/a><\/p>\n\n\n\n<p class=\"has-border-color has-product-main-border-color has-text-color has-white-background-color has-background has-link-color mt-0 pt-0 wp-block-paragraph\" style=\"border-width:2px;margin-top:0;margin-right:0;margin-bottom:var(--wp--preset--spacing--30);margin-left:0;padding-top:var(--wp--preset--spacing--20);padding-right:var(--wp--preset--spacing--20);padding-bottom:var(--wp--preset--spacing--20);padding-left:var(--wp--preset--spacing--20)\">This item downloads Choice Data in specified 2 columns from Google Sheet.<\/p>\n\n\n\n<figure class=\"wp-block-image alignright has-custom-border is-style-default\"><img decoding=\"async\" src=\"data:image;base64,\niVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAADbElEQVRYR8WXS0hUURjH\/2dGJFDz\nlqNimmORhAU5hRVFojgTQSTarl3azN5x1aboQdAqGoVc2AyOFoGCpQuN7E6MRIVW4BBZUqJkbYTw\n+H7M48Sdx3Ve986d0crFuDjfd8\/v+5\/vcQ7Bf\/4jyezPWfX1IKQKDDoAHIj\/P8AwBoCCYAwETnqF\n71f63YQAXEc1B19aE8DMAOGUfZhRgFig8rTQRieV85EF4Gw1DQC5r3zj6K0YBUMjNTn6pCAkATir\n3gJCmpRFnNDKQo18czyruACcVW8HIZcTfjYpA2anRkdjtEsMQHjkvefuoqawQvTxMV8w54S8Y2DM\n\/4uhmRGUZhfj9gcbBn+8lcZirIWaHOZwgwiAwJmrOkIGzro2lOeUJozzxcwITuYf8ts1v2lB39Sw\nHMTF8JwQAQLZrp4KT7hkAbj0LMxvLOHquwfonuQlIBiFyrsvVB2bADbDTQA3wr1SARD8F9zLuD7a\njq6JQSklblEjL+yHTQCrQWgk2akC7FCnY2d6hug+uzqHSy+vSapAjY5dIkCwwz2LtlaqwNz6Ihbd\nyxHuK551nHpqSpgLfgWkal4pQLxdlj1rKOqqTVgRAQCbwQmgKlUFUgIAhqmRrw4pMA1CtHIAK541\nLGxEyhxtr1apkbsjMC4SK4AxauKPhhRg8aIIPwLX7294NPEch3fvR\/d3HpUFOuzJ0IhuHuYF\/\/M9\nus\/eUQYgjE8jT5ICGJoZhU5TivbxPpwvPo2izLxNAJ8X9omBFAGs+oRHICjw6tdHHNMcRNvnXtRq\nz2BvZr4I4PZ58PBLfxIAzEWNDp3iJBQAeiYdOJJzAJ1fB2AoOoHCzFwRwOvzon\/6dRIAkUkYd\/RG\n58Do7DgqcsvQ+qkHdSWV0GYVRChwz\/VEOUBwMIWqQLhqyTaibU9CxvxDSXErFspwyb0q3VgAqAiB\nRkkZMsxTE++v120ZRik0ojjDSBjH3rTp8IH0V1oxwzzUnpKYcRxoyZEXkqELrTieVyYru9Si0DW1\nj+tjl4NnH1qQvZKltLO8kyi9JEBwOm7\/pZSxTmpyNETz\/atreUzksgqEFv0XFRB79E1J8dEICQfW\nkNLDRIQIPM3MYDArBhE2JhCeZpYtPc2iIw0ogmoQogMYB5DygA1zAYSCMeGR6pSLWHEOKJZ5i4Z\/\nAEwJzTC2ALrNAAAAAElFTkSuQmCC\n\" alt=\"Auto Step icon\" class=\"has-border-color has-ast-global-color-4-border-color\" style=\"border-width:0.5em\"\/><\/figure>\n\n\n\n<div class=\"wp-block-coblocks-accordion\">\n<div class=\"wp-block-coblocks-accordion-item\"><details open><summary class=\"wp-block-coblocks-accordion-item__title\">Basic Configs<\/summary><div class=\"wp-block-coblocks-accordion-item__content\">\n<dl style=\"0 0 -0.7em 0\">\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #C3C3C3;\"><\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">Step Name<\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #C3C3C3;\"><\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">Note<\/dd>\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-coblocks-accordion\">\n<div class=\"wp-block-coblocks-accordion-item\"><details open><summary class=\"wp-block-coblocks-accordion-item__title\">Configs for this Auto Step<\/summary><div class=\"wp-block-coblocks-accordion-item__content\">\n<dl style=\"0 0 -0.7em 0\">\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_OAuth2<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C1: OAuth2 Setting<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdB<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C2: Source Spreadsheet ID<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdC<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C3: Source Sheet Title<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdD<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C4: Range of Choice IDs (e.g. &#8220;A1:A&#8221;)<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdE<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C5: Range of Choice Labels (e.g. &#8220;D2:D&#8221;)<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdF<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C6: Data item that will save Choice IDs<span style=\"color:#990000;\"> *<\/span><\/dd>\n<dt style=\"font-weight: 300; font-size: 50%; margin: -1em 0 0 0; color: #aaa;\">conf_DataIdG<\/dt><dd style=\"margin: -0.5em 0 0.1em 1em\">C7: Data item that will save Choice Labels<span style=\"color:#990000;\"> *<\/span><\/dd>\n\n<\/dl>\n<\/div><\/details><\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-lightbulb-exclamation\"><\/i> Notes<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>For C4 and C5, specify a range of cells in a single column\n<ul class=\"wp-block-list\">\n<li>The range should be specified in A1 notation (also known as A1 reference style) (e.g., A1:A4, A:A)\n<ul class=\"wp-block-list\">\n<li>For more information, refer to the <a href=\"https:\/\/developers.google.com\/workspace\/sheets\/api\/guides\/concepts?hl=en#a1-notation\" data-type=\"link\" data-id=\"https:\/\/developers.google.com\/workspace\/sheets\/api\/guides\/concepts?hl=en#a1-notation\" target=\"_blank\" rel=\"noreferrer noopener\">\u201cA1 notation\u201d section in the &#8220;<strong>Google Sheets API Overview<\/strong>&#8220;<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>It is assumed that the number of areas specified by C4 and C5 is the same, and that there are no empty cells in the middle of the area.<\/li>\n\n\n\n<li>File id in the spreadsheet is included in the url.\u3000https:\/\/docs.google.com\/spreadsheets\/d\/SPREADSHEETID\/edit#gid=0<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-images\"><\/i> Capture<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" decoding=\"async\" data-attachment-id=\"103897\" data-permalink=\"https:\/\/support.questetra.com\/en\/bpmn-icons\/googlesheets-getidslabels\/attachment\/google-spreadsheets-%e9%81%b8%e6%8a%9e%e8%82%a2%e3%83%87%e3%83%bc%e3%82%bf%e8%8b%b1%e8%aa%9e\/\" data-orig-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/03\/Google-Spreadsheets-%E9%81%B8%E6%8A%9E%E8%82%A2%E3%83%87%E3%83%BC%E3%82%BF%E8%8B%B1%E8%AA%9E.png?fit=637%2C574&amp;ssl=1\" data-orig-size=\"637,574\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Google-Spreadsheets-\u9078\u629e\u80a2\u30c7\u30fc\u30bf\u82f1\u8a9e\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2021\/03\/Google-Spreadsheets-%E9%81%B8%E6%8A%9E%E8%82%A2%E3%83%87%E3%83%BC%E3%82%BF%E8%8B%B1%E8%AA%9E.png?fit=637%2C574&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2024\/07\/Google-%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88-%E9%81%B8%E6%8A%9E%E8%82%A2%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E4%B8%80%E6%8B%AC%E5%8F%96%E5%BE%97-en-16-1.png?ssl=1\" alt=\"\" class=\"wp-image-103897\" style=\"border:10px solid #aaaaaa; padding:5px; margin:5px;\"><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><i class=\"fal fa-balance-scale\"><\/i> See also<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/support.questetra.com\/en\/developer-blog\/choices-google-sheets\/\">Questetra Developers Blog: Utilize Customer Data on Google Sheets as Choices<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/support.questetra.com\/en\/bpmn-icons\/intermediate-error-catch-event-boundary-type\/\" target=\"_blank\" rel=\"noreferrer noopener\">Intermediate Error Catch Event (Boundary Type)<\/a><\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-coblocks-accordion\">\n<div class=\"wp-block-coblocks-accordion-item\"><details><summary class=\"wp-block-coblocks-accordion-item__title\">Script (click to open)<\/summary><div class=\"wp-block-coblocks-accordion-item__content\">\n<ul class=\"wp-block-list\">\n<li>An XML file that contains the code below is available to download\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/raw.githubusercontent.com\/Questetra\/Addon-XML\/release-2024-05-30\/google-sheets-choice-batchget.xml\" target=\"_blank\" rel=\"noreferrer noopener\">google-sheets-choice-batchget.xml<\/a> (C) Questetra, Inc. (MIT License)<\/li>\n\n\n\n<li>If you are using Professional, you can modify the contents of this file and use it as your own add-on auto step<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-js\" data-lang=\"JavaScript\"><code>\n\n\/\/ OAuth2 config\n\/\/ Authorization Endpoint URL: https:\/\/accounts.google.com\/o\/oauth2\/auth?access_type=offline&approval_prompt=force\n\/\/ Token Endpoint URL: https:\/\/accounts.google.com\/o\/oauth2\/token\n\/\/ Scope: https:\/\/www.googleapis.com\/auth\/spreadsheets.readonly\n\/\/ Consumer Key: (Get by Google Developers Console)\n\/\/ Consumer Secret: (Get by Google Developers Console)\nfunction main() {\n    \/\/\/\/ == \u5de5\u7a0b\u30b3\u30f3\u30d5\u30a3\u30b0\u306e\u53c2\u7167 \/ Config Retrieving ==\n    const oauth = configs.getObject(&quot;conf_OAuth2&quot;);\n    const docId = retrieveStringData( &quot;conf_DataIdB&quot;, &quot;Spreadsheet ID&quot; );\n    const sheet = retrieveStringData( &quot;conf_DataIdC&quot;, &quot;Spreadsheet Title&quot; );\n    const rangeI = configs.get(&quot;conf_DataIdD&quot;);\n    const rangeL = configs.get(&quot;conf_DataIdE&quot;);\n    const idDataDef = configs.getObject(&quot;conf_DataIdF&quot;);\n    const labelDataDef = configs.getObject(&quot;conf_DataIdG&quot;);\n\n    \/\/\/\/ == \u6f14\u7b97 \/ Calculating ==\n    const { idList, labelList } = getChoice(oauth, docId, sheet, rangeI, rangeL);\n\n    \/\/\/\/ == \u30ef\u30fc\u30af\u30d5\u30ed\u30fc\u30c7\u30fc\u30bf\u3078\u306e\u4ee3\u5165 \/ Data Updating ==\n    engine.setData(idDataDef, idList.join(&quot;\\n&quot;));\n    engine.setData(labelDataDef, labelList.join(&quot;\\n&quot;));\n}\n\n\/**\n * \u6587\u5b57\u5217\u30c7\u30fc\u30bf\u3092 config \u304b\u3089\u8aad\u307f\u51fa\u3059\u3002\u7a7a\u3067\u3042\u308c\u3070\u30a8\u30e9\u30fc\u3002\n * @param  {String} confName config \u540d\n * @param  {String} label \u30a8\u30e9\u30fc\u30e1\u30c3\u30bb\u30fc\u30b8\u7528\u306e\u30e9\u30d9\u30eb\n * @return {String} string \u6587\u5b57\u5217\u30c7\u30fc\u30bf\n *\/\nfunction retrieveStringData( confName, label ){\n    const dataDef = configs.getObject( confName );\n    let string;\n    if (dataDef !== null) {\n        string = engine.findData(dataDef);\n    } else {\n        string = configs.get(confName);\n    }\n    if (string === null || string === &quot;&quot;) {\n        throw new Error(`${label} is empty.`);\n    }\n    return string;\n}\n\n\/**\n * Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e\u9078\u629e\u80a2\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\n * @param {AuthSettingWrapper} oauth2  OAuth2 \u8a8d\u8a3c\u8a2d\u5b9a\n * @param {String} docId \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e ID\n * @param {String} sheet \u30b7\u30fc\u30c8\u540d\n * @param {String} rangeI \u9078\u629e\u80a2 ID \u306e\u9818\u57df\n * @param {String} rangeL \u9078\u629e\u80a2 \u30e9\u30d9\u30eb \u306e\u9818\u57df\n * @return {Object} obj \u9078\u629e\u80a2\u30c7\u30fc\u30bf\u3092\u683c\u7d0d\u3057\u305f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\n * @return {Array&lt;String&gt;} obj.idList \u9078\u629e\u80a2 ID \u3092\u683c\u7d0d\u3057\u305f\u914d\u5217\n * @return {Array&lt;String&gt;} obj.labelList \u9078\u629e\u80a2\u30e9\u30d9\u30eb\u3092\u683c\u7d0d\u3057\u305f\u914d\u5217\n *\/\nfunction getChoice(oauth, docId, sheet, rangeI, rangeL) {\n    \/\/ Method: spreadsheets.values.batchGet\n    const uri = `https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/${docId}\/values\/:batchGet`;\n    const response = httpClient.begin()\n        .authSetting(oauth)\n        .queryParam(&quot;ranges&quot;, `${sheet}!${rangeI}`)\n        .queryParam(&quot;ranges&quot;, `${sheet}!${rangeL}`)\n        .queryParam(&quot;valueRenderOption&quot;, &quot;UNFORMATTED_VALUE&quot;)\n        .queryParam(&quot;dateTimeRenderOption&quot;, &quot;FORMATTED_STRING&quot;)\n        .queryParam(&quot;majorDimension&quot;, &quot;COLUMNS&quot;)\n        .get(uri);\n    const status = response.getStatusCode();\n    const responseStr = response.getResponseAsString();\n    if (status !== 200) {\n        const accessLog = `---GET request--- ${status}\\n${responseStr}`;\n        engine.log(accessLog);\n        throw new Error(`Failed to get. status:${status}`);\n    }\n    const jsonObj = JSON.parse(responseStr);\n\n    const I_LABEL = &quot;Choice-ID&quot;;\n    const L_LABEL = &quot;Label&quot;;\n    const idCols = jsonObj.valueRanges[0].values;\n    const labelCols = jsonObj.valueRanges[1].values;\n    \/\/ error for empty column, multiple columns\n    checkCols(idCols, I_LABEL);\n    checkCols(labelCols, L_LABEL);\n\n    const idList = idCols[0];\n    const labelList = labelCols[0];\n    \/\/ error for different number of cells\n    if (idList.length !== labelList.length) {\n        throw new Error(&quot;Number of cells in two ranges is different.&quot;);\n    }\n    if (idList.length &gt; 150000) {\n        throw new Error(&quot;Number of Choice Data is over 150,000.&quot;);\n    }\n    \/\/ error for an empty cells, over 1000 characters\n    checkData(idList, I_LABEL);\n    checkData(labelList, L_LABEL);\n    \/\/ error for same ids\n    const idSet = new Set(idList); \/\/ idList \u306e\u91cd\u8907\u78ba\u8a8d\u7528\u306e Set\n    if (idSet.size !== idList.length) {\n        throw new Error(`Same ${I_LABEL}s are in range.`);\n    }\n\n    return { idList, labelList };\n}\n\n\/**\n * \u5217\u3092\u78ba\u8a8d\u3057\u3001\u7a7a(undefined)\u3084\u8907\u6570\u5217\u3042\u308b\u5834\u5408\u306f\u30a8\u30e9\u30fc\n * @param {Array} cols \u5217\u30c7\u30fc\u30bf\u306e\u914d\u5217\n * @param {String} label \u30a8\u30e9\u30fc\u7528\u306e\u30e9\u30d9\u30eb\n *\/\nfunction checkCols(cols, label) {\n    if (cols === undefined) {\n        throw new Error(`No Data in ${label}s range.`);\n    }\n    if (cols.length &gt; 1) {\n        throw new Error(`Multiple Columns in ${label}s range.`);\n    }\n}\n\n\/**\n * \u914d\u5217\u306e\u8981\u7d20\u3092\u78ba\u8a8d\u3057\u3001\u7a7a\u6587\u5b57\u30841000\u5b57\u4ee5\u4e0a\u306e\u30c7\u30fc\u30bf\u304c\u3042\u308c\u3070\u30a8\u30e9\u30fc\n * @param {Array&lt;String&gt;} list \u30c7\u30fc\u30bf\u304c\u683c\u7d0d\u3055\u308c\u305f\u914d\u5217\n * @param {String} label \u30a8\u30e9\u30fc\u7528\u306e\u30e9\u30d9\u30eb\n *\/\nfunction checkData(list, label) {\n    list.forEach(str =&gt; {\n        if (str === &quot;&quot;) {\n            throw new Error(`Empty Cell is in ${label}s range.`);\n        }\n        if (str.length &gt; 1000) {\n            throw new Error(`Over 1000-character ${label} is in range.`);\n        }\n    });\n}\n\n<\/code><\/pre><\/div>\n<\/div><\/details><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This item downloads Choice Data in specified 2 columns from Google Sheet.<\/p>\n","protected":false},"author":15,"featured_media":110732,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","_uag_custom_page_level_css":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_wpcom_ai_launchpad_first_post":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false,"jetpack_post_was_ever_published":false},"categories":[551],"tags":[396,3162,378],"class_list":["post-67338","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bpmn-icons","tag-google-sheets-api-v4","tag-google-workspace","tag-json-parse"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1","uagb_featured_image_src":{"full":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1",1200,675,false],"thumbnail":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=440%2C440&ssl=1",440,440,true],"medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=560%2C315&ssl=1",560,315,true],"medium_large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=768%2C432&ssl=1",768,432,true],"large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1024%2C576&ssl=1",1024,576,true],"1536x1536":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1",1200,675,true],"2048x2048":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-portrait-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=900%2C675&ssl=1",900,675,true],"newspack-article-block-square-large":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=1200%2C675&ssl=1",1200,675,true],"newspack-article-block-landscape-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=800%2C600&ssl=1",800,600,true],"newspack-article-block-portrait-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=600%2C675&ssl=1",600,675,true],"newspack-article-block-square-medium":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=800%2C675&ssl=1",800,675,true],"newspack-article-block-landscape-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=600%2C450&ssl=1",600,450,true],"newspack-article-block-portrait-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=450%2C600&ssl=1",450,600,true],"newspack-article-block-square-intermediate":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=600%2C600&ssl=1",600,600,true],"newspack-article-block-landscape-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=400%2C300&ssl=1",400,300,true],"newspack-article-block-portrait-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=300%2C400&ssl=1",300,400,true],"newspack-article-block-square-small":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=400%2C400&ssl=1",400,400,true],"newspack-article-block-landscape-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=200%2C150&ssl=1",200,150,true],"newspack-article-block-portrait-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=150%2C200&ssl=1",150,200,true],"newspack-article-block-square-tiny":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?resize=200%2C200&ssl=1",200,200,true],"newspack-article-block-uncropped":["https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1",1200,675,true]},"uagb_author_info":{"display_name":"suesesayaka","author_link":"https:\/\/support.questetra.com\/en\/author\/suesesayaka\/"},"uagb_comment_info":0,"uagb_excerpt":"This item downloads Choice Data in specified 2 columns from Google Sheet.","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9DiIh-hw6","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":75420,"url":"https:\/\/support.questetra.com\/en\/templates\/customer-master-data-sync-process-20160719\/","url_meta":{"origin":67338,"position":0},"title":"Customer Master Data Sync Process","author":"Hirotaka NISHI","date":"2021-01-14","format":false,"excerpt":"Synchronizes \"Customer Master\" managed in Google Spreadsheet, automatically. Automatically Starts 1:00 every night, connects to Google spreadsheet API, and acquires \"Customer list\". The acquired customer list is reformatted and updated as \"Customer Master (Options XML)\" on the Workflow platform. It also corresponds to a manually starting by a person or\u2026","rel":"","context":"In &quot;Workflow Apps&quot;","block_context":{"text":"Workflow Apps","link":"https:\/\/support.questetra.com\/en\/category\/templates\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/03\/eye-catch-customer-data-master-sync-process-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/03\/eye-catch-customer-data-master-sync-process-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/03\/eye-catch-customer-data-master-sync-process-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/03\/eye-catch-customer-data-master-sync-process-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/03\/eye-catch-customer-data-master-sync-process-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":78460,"url":"https:\/\/support.questetra.com\/en\/developer-blog\/choices-google-sheets\/","url_meta":{"origin":67338,"position":1},"title":"Utilize Customer Data on Google Sheets as Choices","author":"Hirotaka NISHI","date":"2020-04-23","format":false,"excerpt":"Get the Customer List stored in Google Sheet via API, and save\/use it as the Options Master.","rel":"","context":"In &quot;Questetra Developers Blog&quot;","block_context":{"text":"Questetra Developers Blog","link":"https:\/\/support.questetra.com\/en\/category\/developer-blog\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/google-sheet-choice-master-featured-image.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/google-sheet-choice-master-featured-image.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/google-sheet-choice-master-featured-image.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/google-sheet-choice-master-featured-image.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/google-sheet-choice-master-featured-image.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":147822,"url":"https:\/\/support.questetra.com\/en\/templates\/master-synchronization-20230417\/","url_meta":{"origin":67338,"position":2},"title":"Customer Master Synchronisation App, Google Sheet Integration","author":"Tsuyoshi Kusaka","date":"2023-07-10","format":false,"excerpt":"This workflow automatically imports the latest customer master. The master data held on Google Sheets is automatically synchronised to the choice master in Questetra. Regardless of whether you have the authority to edit the choice master, if you have the authority to execute this flow, you can execute the synchronisation\u2026","rel":"","context":"In &quot;Workflow Apps&quot;","block_context":{"text":"Workflow Apps","link":"https:\/\/support.questetra.com\/en\/category\/templates\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2023\/07\/master-synchronization-20230417-capture-en.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2023\/07\/master-synchronization-20230417-capture-en.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2023\/07\/master-synchronization-20230417-capture-en.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2023\/07\/master-synchronization-20230417-capture-en.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2023\/07\/master-synchronization-20230417-capture-en.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":119881,"url":"https:\/\/support.questetra.com\/en\/developer-blog\/google-sheets-employee-master\/","url_meta":{"origin":67338,"position":3},"title":"Managing Employee Information in Google Spreadsheets with Workflow Apps","author":"Peter Glover","date":"2022-05-11","format":false,"excerpt":"Introducing an application to manipulate employee information managed in Google Spreadsheets from Questetra.","rel":"","context":"In &quot;Questetra Developers Blog&quot;","block_context":{"text":"Questetra Developers Blog","link":"https:\/\/support.questetra.com\/en\/category\/developer-blog\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2022\/03\/google-sheet-employee-master-fi-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2022\/03\/google-sheet-employee-master-fi-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2022\/03\/google-sheet-employee-master-fi-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2022\/03\/google-sheet-employee-master-fi-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2022\/03\/google-sheet-employee-master-fi-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":67342,"url":"https:\/\/support.questetra.com\/ja\/bpmn-icons\/googlesheets-getidslabels\/","url_meta":{"origin":67338,"position":4},"title":"Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8: \u9078\u629e\u80a2\u30c7\u30fc\u30bf\u306e\u4e00\u62ec\u53d6\u5f97","author":"suesesayaka","date":"2024-07-05","format":false,"excerpt":"\u3053\u306e\u5de5\u7a0b\u306f\u3001Google \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u304b\u3089\u6307\u5b9a\u3057\u305f2\u5217\u306b\u5165\u3063\u3066\u3044\u308b\u9078\u629e\u80a2\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3057\u307e\u3059\u3002","rel":"","context":"In &quot;BPMN\u30a2\u30a4\u30b3\u30f3&quot;","block_context":{"text":"BPMN\u30a2\u30a4\u30b3\u30f3","link":"https:\/\/support.questetra.com\/ja\/category\/bpmn-icons\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2020\/11\/bpmn-icon-google-sheets.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":77695,"url":"https:\/\/support.questetra.com\/en\/templates\/approval-flow-budget-control-20160920\/","url_meta":{"origin":67338,"position":5},"title":"Approval Flow-Budget Control","author":"Hirotaka NISHI","date":"2022-01-17","format":false,"excerpt":"A Proposal-approval flow that automatically records the approved spending in a Google Sheet. When a new Issue is entered at the [1. Submit plan draft] Step, the \u201cBudget Consumption Log\u201d (Google sheet) is referred to, and the total expenditure so far is calculated automatically. At the Step of [2. Decision],\u2026","rel":"","context":"In &quot;Workflow Apps&quot;","block_context":{"text":"Workflow Apps","link":"https:\/\/support.questetra.com\/en\/category\/templates\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/eye-catch-approval-flow-budget-control-20160920-220-en.png?fit=1200%2C675&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/eye-catch-approval-flow-budget-control-20160920-220-en.png?fit=1200%2C675&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/eye-catch-approval-flow-budget-control-20160920-220-en.png?fit=1200%2C675&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/eye-catch-approval-flow-budget-control-20160920-220-en.png?fit=1200%2C675&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/support.questetra.com\/wp-content\/uploads\/2019\/07\/eye-catch-approval-flow-budget-control-20160920-220-en.png?fit=1200%2C675&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"amp_enabled":false,"_links":{"self":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/67338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/comments?post=67338"}],"version-history":[{"count":51,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/67338\/revisions"}],"predecessor-version":[{"id":175056,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/posts\/67338\/revisions\/175056"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media\/110732"}],"wp:attachment":[{"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/media?parent=67338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/categories?post=67338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/support.questetra.com\/en\/wp-json\/wp\/v2\/tags?post=67338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}