Links

Work with Google Sheets

How install plugin

Before installing plugin, create your service account in your google account, for this:
  1. 2.
    Click to credentials->+create credentials->service account
3. Adding role "Editor" for new account
4. After creating service account, visit section "Service account", find you account, and click to Manage key:
5. Adding new key pairs
Choose "JSON" format:
After creating key, google send json file to your computer, please open (!) and copy json string from this file.

Insert JSON service account key to plugin

  1. 1.
    Go to plugin section
  2. 2.
    Find "Google sheets" in "Scenario step" section
  3. 3.
    Insert you json key from google account to fielWithJSON field, for example:

How use plugin in 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 as 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);