Using Google Sheets
Before installing the plugin, create a service account in your Google account. To do this:
- 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.
- 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.
//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 5mo ago