Google Sheets: Download Choice Data
Download Choice Data in specified 2 columns from Google Sheet.
2019-07-19 (C) Questetra, Inc. (MIT License)
Configs
  • C1: OAuth2 Setting Name *
  • C2: Source Spreadsheet ID *
  • C3: Source Sheet Title *
  • C4: Range of Choice IDs (e.g. “A1:A”) *
  • C5: Range of Choice Labels (e.g. “D2:D”) *
  • C6: STRING Data item that will save Choise IDs *
  • C7: STRING Data item that will save Choice Labels *
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. It is premise that the number of values specified by C4 and C5 are the same, and there is no empty cell in the middle of the range.
  2. Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0

See also