spreadsheet-editing

Editing of existing Spreadsheet

This example demonstrates different ways of editing existing Google Spreadsheet file.

• Edit specific cells on the sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);

    Sheet dataSheet = doc.selectSheet("Data");
    dataSheet.setValue("B2", "Some text");
    dataSheet.setValue("C3", 120);
    dataSheet.setValue("D4", DateTime.now());
}

• Edit specific cell range of the sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);

    List<List<String>> sampleData = getSampleData(20, 100);

    //Put range of sample data on sheet
    Sheet dataSheet = doc.selectSheet("Data");
    dataSheet.putRange("D11", sampleData);
}

• Edit sheet table records

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
    Sheet activeSheet = doc.getActiveSheet();

    //Lookup Passengers table on sheet
    Table<Passenger> passengersTable = activeSheet.findTable(Passenger.class, "Passenger Id", "Name");

    //Lookup record by specific condition in the table
    String passengerName = "Wheadon, Mr. Edward H";
    Passenger record = passengersTable.findRecord(r -> passengerName.equals(r.getName()));

    //Edit Age of the record
    record.setAge(110);

    //Update corresponding record on sheet.
    passengersTable.updateRecord(record);
}

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 SpreadsheetEditingModule 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 that has to be edited. 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.