Using Google Sheets

How to Install the Plugin

Before installing the plugin, create a service account in your Google account. To do this:

• Visit https://console.cloud.google.com/apis/dashboard

• Click to Credentials -> Create Credentials -> Service Account

• Add the 'Editor' role for the new account

• After creating the service account, go to the Service Account section, find your account, and click Manage Key

• Add new key pairs

• Choose the JSON format

After creating the key, Google will send a JSON file to your computer. Please open it and copy the JSON string from this file.

• Enable Google API: https://console.cloud.google.com/apis/api/sheets.googleapis.com

Insert the JSON service account key into the plugin

1. Go to the plugin section

2. In the Scenario step section, find Google Sheets

3. Insert your JSON key from your Google account into the 'fieldWithJSON' field.

How to Use the Plugin in the SDK?

Basic Methods

//we could creating document
var xls = $D.sheet.create("test") 
//or you could open existing document
//var xls = "https://docs.google.com/spreadsheets/d/XXX-XXX-SSS/edit"

//now, we could read this xls document first sheet (first tab in XLS)
var data  = $D.sheet.read(xls, "Sheet1")
//output: data = [ [1,2,3] ]


//also, we could write document as
$D.sheet.write(xls, "Sheet1", [ [1,2,3], [4,5,6] ])

Additional Methods Similar to Google Scripts

var ss = $D.sheet.open(xls) //xls link like this: "https://docs.google.com/spreadsheets/d/XXX-XXX-SSS/edit"

//get first page in the sheets
var sheet = ss.getSheets()[0]; 
//show getLastRow and getLastColumn in sheet
$D.console.log("sheet.getLastRow:" + sheet.getLastRow()) 
$D.console.log("sheet.getLastColumn:" + sheet.getLastColumn())

//read values
var values = sheet.getRange('B2:D4').getValues()
for(var r = 0; r<values.length; r++) {
    var row = values[r]
    for(var c = 0; c<row.length; c++) {
      var value = row[c]
      $D.console.log("row:" + r + " c:" + c + " value:" + value)
    }
 }

 //write as google scripts  
  var values = [
    [ "2.000", "1,000,000", "$2.99" ]
  ];
  var range = sheet.getRange("B2:D2");
  range.setValues(values);