Google スプレッドシート: 選択肢データの一括取得 (Google Sheets: Download Choice Data)
Google スプレッドシートから指定した2列に入っている選択肢データを取得します。
https://support.questetra.com/ja/addons/googlesheets-getidslabels/
2019-06-04 (C) Questetra, Inc. (MIT License)
Configs
  • C1: OAuth2 設定名 *
  • C2: ダウンロード元のスプレッドシートの ID *
  • C3: ダウンロード元のシートのタイトル *
  • C4: 選択肢IDの領域情報 (例 “A2:A”, “A:A”) *
  • C5: 選択肢ラベルの領域情報 (例 “B2:B”, “B:B”) *
  • C6: 選択肢IDの一覧を保存する文字列型データ項目 *
  • C7: 選択肢ラベルの一覧を保存する文字列型データ項目 *
Script
// Batch to Get Data via Google Sheets API (ver. 20160921)
// (c) 2016, Questetra, Inc. (the MIT License)

// OAuth2 config
// Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// Token Endpoint URL: https://accounts.google.com/o/oauth2/token
// Scope: https://www.googleapis.com/auth/spreadsheets
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)
main();
function main(){
  //// == 工程コンフィグの参照 / Config Retrieving ==
  const oauth2 = configs.get( "conf_OAuth2" );
  const docId = configs.get( "conf_DataIdB" ) + "";
  const sheet= configs.get( "conf_DataIdC" ) + "";
  const rangeI = configs.get( "conf_DataIdD" ) + ""; // (returns Number)
  const rangeL = configs.get( "conf_DataIdE" ) + ""; // (returns Number)
  const dataIdF = configs.get( "conf_DataIdF" ); // (returns Number)
  const dataIdG = configs.get( "conf_DataIdG" ); // (returns Number)

  //// == ワークフローデータの参照 / Data Retrieving ==
  
  //// == 演算 / Calculating ==
  let accessLog = "";
  let responseJson = "";
  const token = httpClient.getOAuth2Token( oauth2 );

  let choiseIds = "";
  let choiseLabels = "";

  try{
    // Method: spreadsheets.values.batchGet
    const uri = "https://sheets.googleapis.com/v4/spreadsheets/" + docId + "/values/:batchGet" ;

    let response = httpClient.begin()
      .bearer(token)
      .queryParam( "ranges", sheet + "!" + rangeI )
      .queryParam( "ranges", sheet + "!" + rangeL )
      .queryParam( "valueRenderOption", "UNFORMATTED_VALUE" )
      .queryParam( "majorDimension", "COLUMNS" )
      .get( uri );
    accessLog += "---GET request--- " + response.getStatusCode() + "\n";
    responseJson = response.getResponseAsString();
    accessLog += responseJson;
    if( response.getStatusCode() != 200 ){
      throw accessLog;
    }
  }catch(e){
    accessLog += e.message + "\n";
    throw accessLog;
  }
  const jsonObj = JSON.parse( responseJson );
  //error for all cells empty
  if(jsonObj.valueRanges[0].values == null ){
    throw "All Cells in Choice-IDs range are empty.";
  }else if(jsonObj.valueRanges[1].values == null){
    throw "All Cells in Labels range are empty.";
  }
  //error for different number of cells 
  if(jsonObj.valueRanges[0].values[0].length != jsonObj.valueRanges[1].values[0].length){
    throw "Number of cells in two ranges is different.";
  }
  if(jsonObj.valueRanges[0].values[0].length > 150000){
    throw"Number of Choice Data is over 150,000."
  }
  //error for an empty cells,over 1000 characters and add to list
  for( let i = 0; i < jsonObj.valueRanges[0].values[0].length; i++ ){
    if ( jsonObj.valueRanges[0].values[0][i] == ""){
      throw "Empty Cell is in Choice-IDs range.";
    }else if ( jsonObj.valueRanges[0].values[0][i].length > 1000){
      throw "Over 1000-character Choice-ID is in range.";
    }
    choiseIds += jsonObj.valueRanges[0].values[0][i] + "\n";
    if ( jsonObj.valueRanges[1].values[0][i] == ""){
      throw "Empty Cell is in Labels range.";
    }else if ( jsonObj.valueRanges[1].values[0][i].length > 1000){
      throw "Over 1000-character Label is in range.";
    }
    choiseLabels += jsonObj.valueRanges[1].values[0][i] + "\n";
  }
  //error for same ids
  for(let j = 0;j < jsonObj.valueRanges[0].values[0].length;j++){
    for( let k = jsonObj.valueRanges[0].values[0].length -1 ; k > j;k--){
      if (jsonObj.valueRanges[0].values[0][j] === jsonObj.valueRanges[0].values[0][k] ){
        throw "Same Choice-IDs are in range.";
      }
    }
  }
  
  // for Debug
  // accessLog += responseJson + "\n";

  //// == ワークフローデータへの代入 / Data Updating ==
  engine.setDataByNumber( dataIdF, choiseIds );
  engine.setDataByNumber( dataIdG, choiseLabels );
  engine.log(accessLog );
}

Download

Capture

Notes

  1. C4 で指定した領域と C5 で指定した領域の数が同じであること、領域の途中に空セルが無いことが前提となります
  2. スプレッドシートの ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0