2022-06-08 / アドオン / 、、
Google スプレッドシート: Sheet, TSVデータを追記
Google スプレッドシート: Sheet, TSVデータを追記 (Google Sheets: Sheet, Append TSV)
最終行にTSVデータを追加します。必要であれば、新しい行が挿入されます。TSVの指定列だけが追記されます。なお、列IDを指定する際に”d”を付記すれば日時データとして、”n”を付記すれば数値データとして追記されます。列指定の例: “0,2d,5n”。
Configs
  • A: 通信許可設定（OAuth2）を選択してください *
  • B: TSV データをセットしてください *#{EL}
  • C: 送出される列IDをセットしてください (例 “0,1,5d,3n,6”) *#{EL}
  • D: データ挿入先のドキュメントID（Spreadsheet ID）をセットしてください *#{EL}
  • E: データ挿入先のシートID（Sheet ID）をセットしてください#{EL}
  • E2: もしくはデータ挿入先のシート名をセットしてください#{EL}
Script (click to open)
// GraalJS Script (engine type: 2)
/*
NOTES
- An error will occur, if the number of Tab codes on each line is not the same.
    - Skips lines with only Line Break (blank lines)
- Numerical String is evaluated with "parseFloat()" after removing the comma.
    - Nothing is added to the cell that failed to be evaluated.
- Date String is evaluated with "Date()"
    - Nothing is added to the cell that failed to be evaluated.
    - new Date('December 17, 1995 03:24:00')
    - new Date('1995-12-17T03:24:00')
    - new Date('1995-12-17 03:24')
    - new Date('1995-12-17')
        - In case of "YYYY-MM-DD", "T00:00:00" will be added (interpreted as Local Time)
        - Other date-only forms are interpreted as a UTC time.

- TSV文字列の Tab コードの数が全行同数でない場合、エラーとなります
    - 改行のみの行（空行）については、当該行をスキップして読み込みます
- 数値文字列は、カンマ（桁区切り文字）が除去された上で、"parseFloat()" 関数で評価されます
    - 評価に失敗したセルは、何も追記されません
- 日時文字列は、"Date()" 関数で評価されます
    - 評価に失敗したセルは、何も追記されません
    - new Date('December 17, 1995 03:24:00')
    - new Date('1995-12-17T03:24:00')
    - new Date('1995-12-17 03:24')
    - new Date('1995-12-17')
        - なお "YYYY-MM-DD" 書式の場合 "T00:00:00" が自動付加（ローカルタイム化）されます
        - その他の「日付のみの形式」は UTC 時刻と解釈されます
*/

//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strOauthSetting       = configs.get( "OAuth2ConfA" );   // required
const strTsv                = configs.get( "StrConfB" ) + ""; // required
const strColIds             = configs.get( "StrConfC" ) + ""; // required
const strDocId              = configs.get( "StrConfD" ) + ""; // required
let   strSheetId            = configs.get( "StrConfE" ) + "";
let   strSheetName          = configs.get( "StrConfE2") + "";

if( strTsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {B TSV} not specified \n" );
}
if( strColIds === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {C Column IDs} not specified \n" );
}
if( strDocId === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {D Spreadsheet ID} not specified \n" );
}
if( strSheetId === "" ){
  engine.log( " AutomatedTask ConfigWarning:" +
              " Config {E Sheet Id} (recommended) is empty" );
  if( strSheetName === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {E Sheet (both Id and Name)} not specified \n" );
  }
}


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
/// check TSV Dataset
const arrTsv        = strTsv.split("\n");
let arrTsvSpliced = [];
for( let i = 0; i < arrTsv.length; i++ ){ // Remove Blank Lines
  if( arrTsv[i] !== ""){
    arrTsvSpliced.push( arrTsv[i] );
  }else{
    engine.log( " AutomatedTask StringWarning: TSV LineId '[" + i +
                  "]': empty (skipped as dataset)" );
  }
}
let arrTsvCells  = [];
for( let i = 0; i < arrTsvSpliced.length; i++ ){
  arrTsvCells[i] = [];
  let arrTmp = arrTsvSpliced[i].split("\t");
  for( let j = 0; j < arrTmp.length; j++ ){
    arrTsvCells[i].push( arrTmp[j] );
  }
  if( i !== 0 ){
    if( arrTsvCells[0].length !== arrTsvCells[i].length ){
      throw new Error( "\n AutomatedTask UnexpectedTsvError:" +
                       " Dataset TSV not matrix \n" );
    }
  }
}
engine.log( " AutomatedTask TSV Loaded: Number of Rows (Lines) " + arrTsvSpliced.length );
engine.log( " AutomatedTask TSV Loaded: Number of Columns " + arrTsvCells[0].length );

const arrColIds = strColIds.split(",");
engine.log( " AutomatedTask Column Ids to be inserted: " +
              strColIds + " (" + arrColIds.length + ")" );


/// check Spreadsheet (SheetName to SheetId)
const token  = httpClient.getOAuth2Token( strOauthSetting );
if( strSheetId === "" ){
  // prepare request1
  // ref) https://developers.google.com
  //            /sheets/api/reference/rest/v4/spreadsheets/get
  let uri1     = "https://sheets.googleapis.com/v4/spreadsheets/" +
                  strDocId;
  let request1 = httpClient.begin(); // HttpRequestWrapper
      request1 = request1.bearer( token );
  engine.log( " AutomatedTask ApiRequest1 Prepared" );

  // post request1
  const response1     = request1.get( uri1 );       // HttpResponseWrapper
  engine.log( " AutomatedTask ApiRequest1 Start: " + uri1 );
  const response1Code = response1.getStatusCode() + ""; // (primitive string)
  const response1Body = response1.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
  if( response1Code !== "200"){
    throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                      response1Code + "\n" + response1Body + "\n" );
  }

  // parse response1
  /* engine.log( response1Body ); // debug
  response sample
  {
    "spreadsheetId": "17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM",
    "properties": { … },
    "sheets": [
      {
        "properties": {
          "sheetId": 0,
          "title": "sheet1",
          "index": 0,
          "sheetType": "GRID",
          "gridProperties": {
            "rowCount": 100,
            "columnCount": 20,
            "frozenRowCount": 1
          }
        }
      }
    ],
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/17rw......OfHdxxxxRCLM/edit"
  }
  */
  const response1Obj = JSON.parse( response1Body );
  for( let i = 0; i < response1Obj.sheets.length; i++ ){
    engine.log( " - " + response1Obj.sheets[i].properties.title );
    if( response1Obj.sheets[i].properties.title === strSheetName ){
      strSheetId = response1Obj.sheets[i].properties.sheetId;
      engine.log( " AutomatedTask SheetName '" + strSheetName +
                    "': SheetId " + strSheetId );
      break;
    }
  }
  if( strSheetId === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {E2 Sheet Name} does not exist \n" );

  }
}// end_of: if( strSheetId === "" )


/// append Dataset to Sheet
// prepare request2
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest
let request2Obj = {};
    request2Obj.requests = [];
    request2Obj.requests[0] = {};
    request2Obj.requests[0].appendCells = {};
    request2Obj.requests[0].appendCells.rows = [];
    request2Obj.requests[0].appendCells.sheetId = strSheetId;
    request2Obj.requests[0].appendCells.fields = "*";
const regBpmsYMD = /^\d{4}-\d{2}-\d{2}$/;
for( let i = 0; i < arrTsvCells.length; i++ ){
  request2Obj.requests[0].appendCells.rows[i] = {};
  request2Obj.requests[0].appendCells.rows[i].values = [];
  for( let j = 0; j < arrColIds.length; j++ ){
    request2Obj.requests[0].appendCells.rows[i].values[j] = {};
    if( arrColIds[j].slice(-1) === "n" ){ // Numeric Type
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue =
                parseFloat( arrTsvCells[i][parseInt( arrColIds[j] )].replace(/,/g, "") );
    }else if( arrColIds[j].slice(-1) === "d" ){ // Date Type in Sheet
      let strDatetimeTmp = arrTsvCells[i][parseInt( arrColIds[j] )];
      if( regBpmsYMD.test( strDatetimeTmp ) ){
        strDatetimeTmp += "T00:00:00";
      }
      let dateTmp   = new Date( strDatetimeTmp );
      let numSerial = dateTmp.getTime() / 86400000 + 25569;
      // - https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption
      // 25569 means 1970-01-01 00:00:00, 86400 means the number of seconds per day
      numSerial += engine.getTimeZoneOffsetInMinutes() / (60 * 24); // WorkflowPlatform TimeZone
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat = {};
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat = {};
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat.type = "DATE";
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue =
                                                                               numSerial;
    }else{
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
      request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.stringValue =
                                                arrTsvCells[i][parseInt( arrColIds[j] )];
    }
  }
}
let uri2     = "https://sheets.googleapis.com/v4/spreadsheets/" +
                strDocId + ":batchUpdate";
let request2 = httpClient.begin(); // HttpRequestWrapper
    request2 = request2.bearer( token );
    request2 = request2.body( JSON.stringify( request2Obj ), "application/json" );
engine.log( " AutomatedTask ApiRequest2 Prepared" );

// post request2
const response2     = request2.post( uri2 );       // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest2 Start: " + uri2 );
const response2Code = response2.getStatusCode() + ""; // (primitive string)
const response2Body = response2.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse2 Status: " + response2Code );
if( response2Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response2Code + "\n" + response2Body + "\n" );
}

// parse response2
// (nothing)
/* engine.log( response2Body ); // debug
response sample
{
  "spreadsheetId": "1rviXXXXXHcRYYYYjFN1ZZZZfM6EWWWWqifaVVVVzgSM",
  "replies": [
    {}
  ]
}
*/



//// == Data Updating / ワークフローデータへの代入 ==
// (nothing)


} //////// END "main()" /////////////////////////////////////////////////////////////////



/*
APPENDIX
- "SpreadsheetID" and "SheetID" in URL
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=SHEETID
- To Get "ClientId" and "Secret"
    - Access to https://console.developers.google.com/apis/credentials
- This request requires authorization with at least one of the following scopes:
    - https://www.googleapis.com/auth/drive
    - https://www.googleapis.com/auth/drive.file
    - https://www.googleapis.com/auth/spreadsheets
- OAuth Setting sample
    - "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
- Error Log sample (Tab codes not constant)
    - AutomatedTask UnexpectedTsvError: Dataset TSV not matrix 
*/

Download

2020-10-13 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/ja/addons/google-sheets-sheet-append-tsv/
Addonファイルのインポートは Professional でのみご利用いただけます

Notes

  • TSV文字列の Tab コードの数が全行同数でない場合、エラーとなります
    • 改行のみの行（空行）については、当該行をスキップして読み込みます
  • 数値文字列は、カンマ（桁区切り文字）が除去された上で、”parseFloat()” 関数で評価されます
    • 評価に失敗したセルは、何も追記されません
  • 日時文字列は、”Date()” 関数で評価されます
    • 評価に失敗したセルは、何も追記されません
    • new Date(‘December 17, 1995 03:24:00’)
    • new Date(‘1995-12-17T03:24:00’)
    • new Date(‘1995-12-17 03:24’)
    • new Date(‘1995-12-17’)
      • なお “YYYY-MM-DD” 書式の場合 “T00:00:00” が自動付加（ローカルタイム化）されます
      • その他の「日付のみの形式」は UTC 時刻と解釈されます

Capture

最終行にTSVデータを追加します。必要であれば、新しい行が挿入されます。TSVの指定列だけが追記されます。なお、列IDを指定する際に"d"を付記すれば日時データとして、"n"を付記すれば数値データとして追記されます。列指定の例: "0,2d,5n"。

Appendix

See also

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。

%d人のブロガーが「いいね」をつけました。