Google Sheets: Sheet Row, Update with Singleline TSV
Google Sheets: Sheet Row, Update with Singleline TSV
Updates a Google Sheet with a single line of TSV data. Searches for an A-Column cell that exactly matches the first value of the TSV and overwrite only the first occurrence. The values will be parsed as if they were entered manually by the user. If no matching line exists, it will be appended at the end.
Configs
  • A: Select Authorization Setting (OAuth2) *
  • B: Set Singleline TSV dataset *#{EL}
  • C: Set ID of Spreadsheet into which Dataset will be updated *#{EL}
  • D: Set Name of Sheet into which Dataset will be updated#{EL}
Script (click to open)
// GraalJS Script (engine type: 2)
/*
NOTES
- Refers and updates the first visible sheet if sheet name is not specified.
- If row deletion or sort is performed at the same time, different record may be updated.
- When the maximum row of the sheet is reached, an error will occur. (Not appended)
- If no data in column A, an error will occur. (Not appended)

- シート名が無指定の場合、先頭シート(表示されているもの)を参照・更新します。
- 同時に、行削除や並替処理がなされた場合、異なるレコードが更新されてしまう可能性があります。
- シートの最大行に達した際は、エラー終了します(追記されません)
- A列に1つのデータも存在しない場合は、エラー終了します(追記されません)
*/

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

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

if( strSingleTsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {B TSV} not specified \n" );
}
if( strDocId === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {C Spreadsheet ID} not specified \n" );
}
if( strSheetName === "" ){
  engine.log( " AutomatedTask ConfigWarning:" +
              " Config {E Sheet Name} is empty, the first visible sheet is refered" );
}


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


//// == Calculating / 演算 ==

//// Check Tsv Dataset
const arrTsvCells = strSingleTsv.split("\t");
engine.log( " AutomatedTask Singleline TSV Loaded: Number of Columns " + arrTsvCells.length );


//// Get CellValues from the Sheet (Api Request 1)

// prepare request1
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets.values/get
const token  = httpClient.getOAuth2Token( strOauthSetting );
let strRange = strSheetName ? (strSheetName + "!A:A") : "A:A";
engine.log( " AutomatedTask Range to Get: " + strRange );
let getUri1  = "https://sheets.googleapis.com/v4/spreadsheets/" +
                strDocId + "/values/" + strRange;
let request1 = httpClient.begin(); // HttpRequestWrapper
    request1 = request1.bearer( token );
    request1 = request1.queryParam( "majorDimension", "ROWS" ); 
    request1 = request1.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
               // If formatted as currency, return "$1.23" not "1.23".
    request1 = request1.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
               // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
engine.log( " AutomatedTask ApiRequest1 Prepared" );

// request1
const response1     = request1.get( getUri1 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + getUri1 );
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( response2Body ); // debug
response sample
{
  "range": "'country-code'!A1:A1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Afghanistan"
    ],
    [
      "Aland Islands"
    ],
    [
      "Zimbabwe"
    ]
  ]
}
*/
const response1Obj = JSON.parse( response1Body );

if(! 'values' in response1Obj){
  throw new Error( "\n AutomatedTask UnexpectedSheetError: " +
                    "No Value in " + strRange + "\n" );
}
engine.log( " AutomatedTask ApiResponse #of Values: " + response1Obj.values.length );
let arrColAValues  = new Array( response1Obj.values.length );
for( let i = 0; i < response1Obj.values.length; i++ ){
  arrColAValues[i] = response1Obj.values[i] ? response1Obj.values[i][0] : "";
}


//// Search "arrTsvCells[0]" in "arrColAValues"
let numMatched = arrColAValues.length; // NoMatch Case
for( let i = 0; i < arrColAValues.length; i++ ){
  if( arrColAValues[i] === arrTsvCells[0] ){
    numMatched = i;  // Match Case
    break;
  }
}


//// Update Values of the Sheet (Api Request 2)

// prepare request2
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets.values/update
let strRange2 = strSheetName ?
                strSheetName + "!" + (numMatched + 1) + ":" + (numMatched + 1) :
                (numMatched + 1) + ":" + (numMatched + 1);
engine.log( " AutomatedTask Range to Update: " + strRange2 );
let request2Obj = {};
    request2Obj.values = [];
    request2Obj.values[0] = strSingleTsv.split("\t");
let request2 = httpClient.begin(); // HttpRequestWrapper
    request2 = request2.bearer( token );
    request2 = request2.queryParam( "valueInputOption", "USER_ENTERED" ); 
    request2 = request2.body( JSON.stringify( request2Obj ), "application/json" );
engine.log( " AutomatedTask ApiRequest2 Prepared" );

// request2
let   putUri2       = "https://sheets.googleapis.com/v4/spreadsheets/" +
                       strDocId + "/values/" + strRange2;
const response2     = request2.put( putUri2 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest2 Start: " + putUri2 );
const response2Code = response2.getStatusCode() + ""; // (primitive string)
const response2Body = response2.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response2Code );
if( response2Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response2Code + "\n" + response2Body + "\n" );
}


//// == 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 (wrong SheetName)
    - "message": "Unable to parse range: NonExistName!A:A",
    - "status": "INVALID_ARGUMENT"
- Error Log sample (Need to expand editing area)
    "message": "Range ('Sheet1'!1001) exceeds grid limits. Max rows: 1000, max columns: 26",
*/

Download

The Addon-import feature is available with Professional edition.

Notes

  • Refers to and updates the first visible sheet if a sheet name is not specified.
  • If row deletion or sorting is performed at the same time, a different record may be updated.
  • When the maximum number of rows in a sheet is reached, an error will occur (no additional information is appended)
  • If there is no data in column A, an error will occur (no additional information is appended)

Capture

Updates Google Sheets with singleline TSV. Searches A-Column cell that exactly matches the first value of the TSV, and overwrite only the first occurrence. The values will be parsed as if the user typed. If no matching line, append to the end.

Appendix

See also

1 thought on “Google Sheets: Sheet Row, Update with Singleline TSV”

  1. Pingback: Google Sheets: Two ValueRanges, Xlookup Reference – Questetra Support

Leave a Reply

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

%d bloggers like this: