# 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](https://console.cloud.google.com/apis/dashboard?pli=1)
* Click to Credentials -> Create Credentials -> Service Account

<figure><img src="https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2FVHHSFfXqnWa5c8rfMLa0%2Fimage.png?alt=media&#x26;token=3cfbe745-8537-4292-adae-50c942fead99" alt=""><figcaption></figcaption></figure>

* Add the 'Editor' role for the new account

![](https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2F8ARU4l66YEf6tGEzWYWY%2Fimage.png?alt=media\&token=ac3d8255-6356-4267-bb97-4462c7c911e5)

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

![](https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2F5qqwVsxBteyGTU9cMCjn%2Ftelegram-cloud-photo-size-2-5427002954923227521-y.jpg?alt=media\&token=171e4e5d-91ff-4a18-a061-acfeb1ead549)

* Add new key pairs

![](https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2F809z3xBwhvCvYGjpgvWt%2Fimage.png?alt=media\&token=9cc5135d-4244-4285-9460-934fac7c7432)

* Choose the **JSON** format

![](https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2FOlx08KolIpXzzZk31xVL%2Fimage.png?alt=media\&token=c39d7af1-25e2-4c13-b02f-c14a27d98d84)

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.

![](https://3071851461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-M4Nnmtk9_gFGWOddsf6%2Fuploads%2FGYPJjO0dm6H2wN3yqmso%2Fimage.png?alt=media\&token=d7bfe4da-e0fa-4ce8-85b3-60c10e4d2afa)

## 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);
```