spreadsheet-sheets-manipulating

Sheets manipulating

This example demonstrates using of GoogleSheets library functionality to perform different actions with sheets of Spreadsheet.

• List existing sheets

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
     
    List<String> sheetNames = doc.getSheetNames();
    ...
}

• Activate sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);

    //Get currrent active sheet
    Sheet activeSheet = doc.getActiveSheet();
    
    //Activate another sheet by name
    activeSheet = doc.selectSheet("Summary");

    //Activate another sheet by index
    activeSheet = doc.selectSheet(1);
}

• Rename sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);
    Sheet activeSheet = doc.getActiveSheet();
    
    String newSheetName = "Renamed Sheet";
    activeSheet.rename(newSheetName);
}

• Move sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

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

    int newPosition = doc.getSheetNames().size() - 1;
    activeSheet.moveTo(newPosition);
}

• Clone sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

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

    String clonedSheetName = "Cloned Sheet";
    Sheet clonedSheet = activeSheet.cloneAs(clonedSheetName);
    ... 
}

• Delete sheet

@Inject
private GoogleSheets googleSheets;

public void execute() {

    SpreadsheetDocument doc = googleSheets.getSpreadsheet(sourceSpreadsheetFileId);

    List<String> sheetNames = doc.getSheetNames();
    String lastSheetName = sheetNames.get(sheetNames.size() - 1);

    doc.removeSheet(lastSheetName);
}

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 SheetsManipulatingModule 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.