working-with-sheet-rows

Working with sheet rows

This example demonstrates what is possible to do with sheet rows of Google Spreadsheet file using GoogleSheets library.

• Lookup and edit rows

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
    Sheet sheet = doc.selectSheet("Editing");

    Row row = sheet.findRow("Moran, Mr. James");
    row.setValue("F", 99999);
}

• Insert new rows

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
    Sheet sheet = doc.selectSheet("Inserting");

    List<List<String>> data = Arrays.asList(
        Arrays.asList("Value 1 1", "Value 2 1"),
        Arrays.asList("Value 1 2", "Value 2 2") 
    );

    sheet.insertRows(InsertMethod.AFTER, "D10", data);
}

• Delete rows

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
    Sheet sheet = doc.selectSheet("Deleting");

    sheet.removeRow(8);
}

See the full source of this example for more details or check following instructions to run it.

Running

⚠️ To be able to build and run this example it's necessary to have an access to some instance of EasyRPA Control Server.

Its a fully workable process. To play around with it and run do the following:

1. Download this example using link.

2. Unpack it somewhere on local file system.

3. Specify URL to the available instance of EasyRPA Control Server in the pom.xml of this example:

   <repositories>
       <repository>
           <id>easy-rpa-repository</id>
           <url>[Replace with EasyRPA Control Server URL]/nexus/repository/easyrpa/</url>
       </repository>
   </repositories>

4. If necessary, change version of easy-rpa-engine-parent in the same pom.xml to corresponding version of EasyRPA Control Server:

   <parent>
       <groupId>eu.ibagroup</groupId>
       <artifactId>easy-rpa-engine-parent</artifactId>
       <version>[Replace with version of EasyRPA Control Server]</version>
   </parent>

5. Build it using mvn clean install command. This command should be run within directory of this example.

6. Run main() method of WorkingWithSheetRowsModule class.

Configuration

All necessary configuration files can be found in src/main/resources directory.

apm_run.properties

Parameter	Value
google.services.auth.secret	The alias of secret vault entry with OAuth 2.0 Client JSON necessary for authentication on the Google server. For information regarding how to configure OAuth 2.0 Client see OAuth client ID credentials In case of running of this example without EasyRPA Control Server, secret vault entries can be specified in the vault.properties file. The value of secret vault entry in this case should be a JSON string with following structure encoded with Base64: { "installed": { "client_id": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com", "project_id": "XXXXXXX-XXXXXX", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_secret": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", "redirect_uris": [ "urn:ietf:wg:oauth:2.0:oob", "http://localhost" ] } }
source.spreadsheet.file.id	File ID of source Google Spreadsheet file. Expected content of the source spreadsheet can be found in 'source.xlsx' file located at src/main/resources directory. This file can be used for creation of necessary source Google Spreadsheet in Google Drive.