Google スプレッドシート: 行追加 (テーブル型データ)

Google スプレッドシート: 行追加 (テーブル型データ) (Google Sheets: Append New Rows (Table type data))
シート末尾にテーブル型データの値を入力します。必要あれば行領域を拡大します。
2020-04-03 (C) Questetra, Inc. (MIT License)
Configs
  • C1: OAuth2設定名 *
  • C2: 入力先のスプレッドシートの ID *
  • C3: 入力先のシートのタイトル *
  • C4: テーブル型データ項目 *
  • C-A: A 列に追加されるサブデータ項目の番号
  • C-B: B 列に追加されるサブデータ項目の番号
  • C-C: C 列に追加されるサブデータ項目の番号
  • C-D: D 列に追加されるサブデータ項目の番号
  • C-E: E 列に追加されるサブデータ項目の番号
  • C-F: F 列に追加されるサブデータ項目の番号
  • C-G: G 列に追加されるサブデータ項目の番号
  • C-H: H 列に追加されるサブデータ項目の番号
  • C-I: I 列に追加されるサブデータ項目の番号
  • C-J: J 列に追加されるサブデータ項目の番号
Script
// 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 ==
  let dataidArray = [];
  const dataIdT = configs.get( "conf_DataIdT" ); // (returns Number)
  const oauth2 = configs.get( "conf_OAuth2" );
  const spreadsheetId = configs.get( "conf_DataIdW" ) + "";
  const sheetName = configs.get( "conf_DataIdX" ) + "";
  dataidArray[0] = configs.get( "conf_DataIdA" );
  dataidArray[1] = configs.get( "conf_DataIdB" );
  dataidArray[2] = configs.get( "conf_DataIdC" );
  dataidArray[3] = configs.get( "conf_DataIdD" );
  dataidArray[4] = configs.get( "conf_DataIdE" );
  dataidArray[5] = configs.get( "conf_DataIdF" );
  dataidArray[6] = configs.get( "conf_DataIdG" );
  dataidArray[7] = configs.get( "conf_DataIdH" );
  dataidArray[8] = configs.get( "conf_DataIdI" );
  dataidArray[9] = configs.get( "conf_DataIdJ" );
  dataidCheck(dataidArray);
  
  //// == ワークフローデータの参照 / Data Retrieving ==
  const tableData = engine.findDataByNumber( dataIdT );
  // tableData: com.questetra.bpms.core.model.formdata.ListArray
  if(tableData === null){
    throw "the table data is empty.";
  }
  tableDataDef = engine.findDataDefinitionByNumber( dataIdT );
  tableDataList = tableDataDef.getSubDataDefinitions();
  //// == 演算 / Calculating ==
  const token = httpClient.getOAuth2Token( oauth2 );
  
  // check spreadsheet
  const sheetId = checkSheet(token,spreadsheetId,sheetName);
    

  // append records
  const obj = makeAppendData(tableData,sheetId,tableDataList,dataidArray);
  sendAppendData(token,spreadsheetId,obj);
}

/**
  * 追加するデータ項目の番号が設定されているかチェックする
  * @param {Array<String>} dataidArray
  */
function dataidCheck(dataidArray){
  let dataidCheck = 0;
  for(let i = 0; i < dataidArray.length; i++){
    if(dataidArray[i] === null || dataidArray[i] === ""){
      dataidCheck++;
    }
  }
  if(dataidCheck === 10){
    throw "No Data to add is selected.";
  }
}

/**
  * 指定した名前のシートが指定したスプレッドシートにあるかチェックする
  * @param {String} token
  * @param {String} spreadsheetId
  * @param {String} sheetName
  * @return {Number}
  */
function checkSheet(token,spreadsheetId,sheetName){
  let sheetId = -1;
  let uri = "https://sheets.googleapis.com/v4/spreadsheets/";
    uri += spreadsheetId;
    uri += "?includeGridData=false";
  const response = httpClient.begin()
    .bearer(token)
    .get( uri ); 
  engine.log('---GET request--- ' + response.getStatusCode());
  const responseJson = response.getResponseAsString();
  if( response.getStatusCode() !== 200 ){
    engine.log(responseJson);
    throw "Can't get sheet information";
  }
  const jsonObj = JSON.parse( responseJson );
  engine.log(' SPREADSHEET TITLE: \"' + jsonObj.properties.title + '\" has' + jsonObj.sheets.length + ' sheets');
  for( var i = 0; i < jsonObj.sheets.length; i++ ){
    if( jsonObj.sheets[i].properties.title === sheetName ){
      sheetId = parseInt( jsonObj.sheets[i].properties.sheetId, 10 );
      engine.log(' SHEET TITLE: \"' + sheetName + '\" exists (id: " + sheetId + ")');
      return sheetId;
    }
  }
  // error (not exist)
  throw 'Sheet "' + sheetName + '" does not exist';
}

/**
  * 追加用のデータを作る準備をし、makingDataLoop を呼び出す
  * @param {ListArray} tableData
  * @param {Number} sheetId
  * @param {Array<SubDataDefinitionView>} tableDataList
  * @param {Array<String>} dataidArray
  * @return {Object}
  */
function makeAppendData(tableData,sheetId,tableDataList,dataidArray){
  //prepare object
  let obj = {
    requests : [{
      appendCells : {
        rows : [],
        sheetId : sheetId,
        fields : "*"
      }
    }]
  }; 
  makingDataLoop(tableData,tableDataList,dataidArray,obj);
  return obj;
}

/**
  * 追加用のデータを作る
  * @param {ListArray} tableData
  * @param {Array<SubDataDefinitionView>	} tableDataList
  * @param {Array<String>} dataidArray
  * @param {Object} obj
  */
function makingDataLoop(tableData,tableDataList,dataidArray,obj){
  const numOfRows = tableData.size() - 0; // 行(Tableの高さ)
  let dataNumberList = [];
  //make list of table data number
  for(let i = 0;i < tableDataList.length; i++){
    let temp = tableDataList[i].getNumber() + "";
    dataNumberList.push(temp);
  }
  for( let i = 0; i < numOfRows; i++ ){
    obj.requests[0].appendCells.rows[i] = {
      values: new Array(dataidArray.length)
    };
    let flag = true;
    for( let j = dataidArray.length - 1; j > -1; j-- ){
      let cellValue = makeCellValue(i,tableData,tableDataList,dataidArray[j],dataNumberList);
      if(flag && cellValueEmptyCheck(cellValue)) {
        //put null if all latter cells are empty
        obj.requests[0].appendCells.rows[i].values[j] = null;
      }else{
        obj.requests[0].appendCells.rows[i].values[j] = cellValue;
        flag = false;
      }
    }
    if(flag){
      throw "There is an empty row in data to append.";
    }
  }
}

/**
  * セルごとのデータを作る
  * @param {Number} i
  * @param {ListArray} tableData
  * @param {Array<SubDataDefinitionView>	} tableDataList
  * @param {String} dataId
  * @param {Array<String>} dataNumberList
  * @return {Object}
  */
function makeCellValue(i,tableData,tableDataList,dataId,dataNumberList){
  if(dataId === "" || dataId === null ){
    //when selected cell is empty
    return {"userEnteredValue": {"stringValue": "" }};
  }
  const pointedCol = dataNumberList.indexOf(dataId);
  if(pointedCol === -1){
    //when selected column doesn't exist
    throw "Data Number " + dataId + " doesn't exist";
  }
  const cellValue = tableData.get(i, pointedCol );
  if(cellValue.length > 50000){
    //when the number of letters in text is over 50000 
    throw "Can't set text over 50,000 character.";
  }
  if(tableDataList[pointedCol].matchDataType("DECIMAL")){
    return {"userEnteredValue": {"numberValue": cellValue }};
  }else{
    return {"userEnteredValue": {"stringValue": cellValue }};
  }
}

/**
  * セルが空であるかどうかをチェックする
  * @param {Obj} cellValue
  * @return {Boolean}
  */
function cellValueEmptyCheck(cellValue){
  return (cellValue.userEnteredValue.stringValue === null || cellValue.userEnteredValue.stringValue === undefined || cellValue.userEnteredValue.stringValue === "") && 
    (cellValue.userEnteredValue.numberValue === null || cellValue.userEnteredValue.numberValue === undefined || cellValue.userEnteredValue.numberValue === "")
}

/**
  * データを Google Sheets に送信する
  * @param {String} token
  * @param {String} spreadsheetId
  * @param {Object} obj
  */
function sendAppendData(token,spreadsheetId,obj){ 
  let uri = "https://sheets.googleapis.com/v4/spreadsheets/";
      uri += spreadsheetId;
      uri += ":batchUpdate";

  let response = httpClient.begin()
    .bearer(token)
    .body( JSON.stringify( obj ), "application/json" )
    .post( uri ); 
  engine.log('---POST request--- ' + response.getStatusCode());
  if( response.getStatusCode() === 200 ){
    engine.log('ADDED DATA');
  }else if( response.getStatusCode() >= 300 ){
    throw response.getResponseAsString();
  }
}

Download

Capture

Notes

  1. https://docs.google.com/spreadsheets/d/1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0/edit#gid=0 Spreadsheet ID: 1exampleEXAMPLEexampleEXAMPLEexampleEXAMPLE0
%d人のブロガーが「いいね」をつけました。