DataDriven automated testing with GoogleSheets

— 3 min read

TestData cloud integration with googlesheets and java with  google service accounts

In one of my projects I created an datadriven integration between Katalon studio (with custom functions) and the google apis on a team drive. I rewritten it in java eclipse and will also make a sample in nodejs.First start by creating a service account at the Google Developer Console:(Note: we created this example project only for educational purposes: this project I already removed from my domain and cannot be used)

SetupserviceAccount

Select google sheets Api

 

select google sheets api

 

Enable the api

 

enable google api

Create Credentials for a service account

create google service account

create service account

create service account

You have the option to skip the normal registration: you can create directly the service account by clicking on this link.(there are other ways of linking a service account to a project,but this is the one i showed)

Then create a json key which you need to save and store it preferably in your workspace of your project to be created . Save it as json  format.

CreateKey CreateJson

Important notice!

Set this up for the google drive apis as you then have full access to create google documents like google sheets and this will give you the access to update data on drive(and google sheets)

Next step!! Add your service account to google sheets

Before useage in java or in node js you have to make sure that the service account has permissions to retreive and update values

access googlesheets datadriven sheet

Then : create an eclipse project (maven, assuming you have that knowledge) and make sure to use these dependencies

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-drive</artifactId>
    <version>v3-rev192-1.25.0</version>
    </dependency>
    <dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev607-1.25.0</version>
</dependency>
<dependency>
    <groupId>com.google.http-client</groupId>
    <artifactId>google-http-client-gson</artifactId>
    <version>1.34.2</version>
</dependency>
<dependency>
    <groupId>org.seleniumhq.selenium</groupId>
    <artifactId>selenium-java</artifactId>
    <version>4.0.0-alpha-4</version>
</dependency>
<dependency>
    <groupId>org.seleniumhq.selenium</groupId>
    <artifactId>selenium-chrome-driver</artifactId>
    <version>4.0.0-alpha-4</version>
</dependency>

Once you have setup the depencies and the jar libraries are available I have created one googlesheets class with static functions purely for conectivity setup to a google sheet.

Please pay close attention to scopes as with this scope I am allowed to update the google sheet

public class GoogleSheets {
    
       
      public static NetHttpTransport netHttpTransport() throws GeneralSecurityException, IOException {
            return GoogleNetHttpTransport.newTrustedTransport();
        }

        public static JacksonFactory jacksonFactory() {
            return JacksonFactory.getDefaultInstance();
        }

        private static Set<String> googleOAuth2Scopes() {
            Set<String> googleOAuth2Scopes = new HashSet<>();
            googleOAuth2Scopes.add(SheetsScopes.SPREADSHEETS);
            return Collections.unmodifiableSet(googleOAuth2Scopes);
        }

        public static GoogleCredential googleCredential() throws IOException {
            File serviceAccount = new File("c://chromedriver//80//clientsecrets.json");
            return GoogleCredential.fromStream(new FileInputStream(serviceAccount))
                    .createScoped(googleOAuth2Scopes());
        }

        public static Sheets SheetsService() {
            try {
                return new Sheets(netHttpTransport(), jacksonFactory(), googleCredential());
            } catch (GeneralSecurityException | IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return null;
        }

    

    }

In the static function Sheets SheetsSerivce everything comes together with regards to connectivity. This function you can call in the unit test cases. Also note I am using the clientsecrets.json for logging in onto the google sheet with my service account

@Test
    public void searchgooglevalueviagooglesheets()
    {
        //here you call the helper function
       Sheets Sheets = GoogleSheets.SheetsService();
        //set up the spreadsheet id of google sheet
        //add the service user to google sheet
        String spreadsheetId = "id of the spreadsheet";
        //pick up the data in A2
        String rangeid = "A2";
        
        ValueRange response = null;
        try {
            response = Sheets.spreadsheets().values()

                    .get(spreadsheetId, rangeid)
                    .execute();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        List<List<Object>> values = response.getValues();
        System.setProperty("webdriver.chrome.driver","c://chromedriver//80//chromedriver.exe");
        ChromeDriver driver=new ChromeDriver();
        driver.manage().window().maximize();
        driver.get("http://www.google.com");
        String strvalue = values.toString();

           driver.findElementByCssSelector("\[name$=q\]").sendKeys(strvalue);
        //driver.quit();
        
        
    }
}

In the strvalue (Search datadriven google sheets) the value in google sheets B2 is passed to sendkeys and will search accordingly. Congratulations :) This is the first googlesheet search you have created via selenium:

eclipse selenium

and the search was succesfull

Search