Work with Google Sheets
Before installing plugin, create your service account in your google account, for this:
- 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.
- 1.Go to plugin section
- 2.Find "Google sheets" in "Scenario step" section
- 3.Insert you json key from google account to fielWithJSON field, for example:
//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] ])
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);
Last modified 1yr ago