Google Sheets: Append New Row
Adds new cells after the last row with data in a sheet, inserting new rows into the sheet if necessary.
Configs: Common
  • Step Name
  • Note
Configs
  • C1: OAuth2 Setting *
  • C2: Target Spreadsheet ID *
  • C3: Target Sheet Title *
  • C4: Data item to save the appended row number
  • C-A: Column-A Value of New Row#{EL}
  • C-B: Column-B Value of New Row#{EL}
  • C-C: Column-C Value of New Row#{EL}
  • C-D: Column-D Value of New Row#{EL}
  • C-E: Column-E Value of New Row#{EL}
  • C-F: Column-F Value of New Row#{EL}
  • C-G: Column-G Value of New Row#{EL}
  • C-H: Column-H Value of New Row#{EL}
  • C-I: Column-I Value of New Row#{EL}
  • C-J: Column-J Value of New Row#{EL}
Script (click to open)

// OAuth2 config sample at [OAuth 2.0 Setting]
// - 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 spreadsheetId = configs.get( "conf_DataIdW" );
  const sheetName = configs.get( "conf_DataIdX" );
  const rowNumDef = configs.getObject( "conf_RowNum" );

  const columns = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"];
  const dataArray = columns.map( i => configs.get( `conf_DataId${i}` ) );

  //// == Calculating / 演算 ==
  const row = buildRowObj(dataArray);
  const sheetId = getSheetId(oauth2, spreadsheetId, sheetName); // Access to the API 1st(Get GID)
  appendRow(oauth2, spreadsheetId, sheetId, row); // Access to the API 2nd(POST)
  if(rowNumDef === null) return;

  // only when rowNumDef is not null
  const rowNum = getLastRowNum(oauth2, spreadsheetId, sheetName); // Access to the API 3rd(GET)

  //// == ワークフローデータへの代入 / Data Updating ==
  engine.setData(rowNumDef, rowNum);
}

/**
 * 文字列データを所定の形式のセルオブジェクトに変換する。
 * @param  {String} data 文字列データ
 * @return {Object} cellObj 文字列データが格納されたセルオブジェクト
 */
function buildCellObj( data ){
  const cellObj = {};
  cellObj.userEnteredValue = {};
  if( data === "" || data === null){
    cellObj.userEnteredValue.stringValue = "";
    return cellObj;
  }else{ // for "STRING"
    if(data.length > 50000){
      throw "Can't set text over 50,000 characters.";
    }
    cellObj.userEnteredValue.stringValue = data;
    return cellObj;
  }
}

/**
 * 文字列データを所定の形式の行オブジェクトに変換する。
 * @param  {Array<String>} dataArray 文字列データの配列
 * @return {Object} rowObj 文字列データが格納された行オブジェクト
 */
function buildRowObj( dataArray ){
  const rowObj = {};
  rowObj.values = dataArray.map( data => buildCellObj( data ) );

  // 後ろから空白の要素数を数える
  let count = 0;
  for(let i = rowObj.values.length; i > 0; i--) {
    if(rowObj.values[i-1].userEnteredValue.stringValue === ""){
      count++;
    }else{ // 空白でなければ、ループから抜ける
      break;
    }
  }

  // 空白部分を削除
  if(count > 0){
    rowObj.values = rowObj.values.slice(0, -count);
  }

  // すべてが空白ならエラー
  if(rowObj.values.length === 0){
    throw "No Data to add is set.";
  }

  return rowObj;
}

/**
 * GET リクエストを送信し、シート ID (GID) を取得する。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {String} sheetName シート名
 * @return {Number} sheetId シート ID (GID)
 */
function getSheetId(oauth2, spreadsheetId, sheetName){
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}?includeGridData=false`;
  const response = httpClient.begin()
    .authSetting(oauth2)
    .get(uri);
  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if (status !== 200) {
    engine.log(responseStr);
    throw `Failed to get sheet information. status: ${status}`;
  }
  const jsonObj = JSON.parse(responseStr);
  engine.log(`SPREADSHEET (TITLE: ${jsonObj.properties.title}) has ${jsonObj.sheets.length} sheets`);
  for (let i = 0; i < jsonObj.sheets.length; i++) {
    if (jsonObj.sheets[i].properties.title === sheetName) {
      const 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`;
}

/**
 * 行追加のリクエストボディを生成する。
 * @param  {Number} sheetId シート ID (GID)
 * @return {Object} row 行オブジェクト
 */
function buildRequestObj(sheetId, row){
  const requestObj = {};
  requestObj.requests = [];
  requestObj.requests[0] = {};
  requestObj.requests[0].appendCells = {};
  requestObj.requests[0].appendCells.sheetId = sheetId;
  requestObj.requests[0].appendCells.fields = "*";
  requestObj.requests[0].appendCells.rows = [];
  requestObj.requests[0].appendCells.rows[0] = row;
  return requestObj;
}

/**
 * 行追加の POST リクエストを送信する。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {Number} sheetId シート ID (GID)
 * @param {Object} row 行オブジェクト
 */
function appendRow(oauth2, spreadsheetId, sheetId, row){
  const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}:batchUpdate`;
  const requestObj = buildRequestObj(sheetId, row);
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( JSON.stringify( requestObj ), "application/json" )
    .post( apiUri ); // HttpResponseWrapper
  const httpStatus = response.getStatusCode();
  if( httpStatus !== 200 ){
    engine.log(response.getResponseAsString());
    throw `Failed to append data. status: ${httpStatus}`;
  }
}

/**
 * GET リクエストを送信し、データが入力されている最終行の行番号を返す。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {String} sheetName シート名
 * @return {String} データが入力されている最終行の行番号
 */
function getLastRowNum(oauth2, spreadsheetId, sheetName){
  const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}/values/${encodeURIComponent(sheetName)}`;
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .queryParam( "majorDimension", "ROWS" )
    .get( apiUri ); // HttpResponseWrapper
  const httpStatus = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if( httpStatus !== 200 ){
    engine.log(responseStr);
    throw `Failed to get rows in the sheet. status: ${httpStatus}`;
  }
  const jsonObj = JSON.parse(responseStr);
  return jsonObj.values.length.toString();
}

Notes

  • Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0

Capture

See also

Appendix

  • google-sheets-row-append.xml (C) Questetra, Inc. (MIT License)
    • If you are using Professional, you can modify the contents of this file and use it as your own add-on
%d bloggers like this: