Google Sheets: Sheet, Append TSV
Google Sheets: Sheet, Append TSV
Adds the TSV dataset after the last row. New rows will be inserted into the sheet if necessary. Only the specified columns of TSV are added, column ID with an identifier “d” as Datetime, and column ID with an identifier “n” as Numeric; eg “0,2d,5n”.
Configs
  • A: Select Authorization Setting (OAuth2) *
  • B: Set TSV dataset *#{EL}
  • C: Set Column IDs to be Thrown (e.g. “0,1,5d,3n,6”) *#{EL}
  • D: Set ID of Spreadsheet into which Dataset will be inserted *#{EL}
  • E: Set ID of Sheet into which Dataset will be inserted#{EL}
  • E2: Or, Set Name of Sheet into which Dataset will be inserted#{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/addons/google-sheets-sheet-append-tsv/
The Addon-import feature is available with Professional or Enterprise edition.

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.

Capture

Adds the TSV dataset after the last row. New rows will be inserted into the sheet if necessary. Only the specified columns of TSV are added, column ID with an identifier "d" as Datetime, and column ID with an identifier "n" as Numeric; eg "0,2d,5n".

Appendix

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: