How to Read Data From Google Sheets

Published in

Geek Culture

5 min readApr 11, 2021

In test automation, there are several kinds of frameworks to choose from to implement in your project depending on your needs. One of which is the Data-Driven Framework. In this framework, test data are stored in a single or combination of data sources like .csv, .xls .xlsx, database, straight from google sheets, and many more. With that, you should have a utility to extract or read your data from these data sources. If you haven’t tried reading data from an excel file yet, you can check this How to Read Data From Excel File Using Apache POI. In this writing, we will be reading our data straight from google sheets.

To read data from google sheets we will be using the Google Sheets API provided by Google. Actually, Google has already provided us very good documentation and example on how to use their API. So what we’re going to do is go through the “Enabling the Google Sheets API” for your google account and a short walkthrough to a simple utility class for reading data from google sheets.

Enabling the Google Sheets API

Open this link. You will be redirected to Google Sheets API Quickstart guide for Java

2. Look and click on Enable the Google Sheets API. A prompt will show asking for the project name. For our purpose, any name will do, the default value is even enough.

3. Click Next. It will try to enable the API and then another prompt to configure your OAuth client will pop up. Again, for our purpose, “Desktop App” will do.

4. Click Create. Another prompt will show displaying your Client ID and Client secret and an option to Download Client Configuration.

5. Clicking on Download Client Configuration, the configuration file will be downloaded as json. You can rename the file if you want or just leave it be. We will be adding that file to our automation project or whatever project you are working on. We will be using the content of that file to get ourselves authorized when reading our data on google sheets.

Here are the methods of my simple utility class for reading data in google sheets. The methods just throws Exception and no other exception handling were made for simplification. But for these methods to work, you should add these libraries or just add as dependency to your maven project.

For the api client library, we will be using 1.22.0, this is not the latest version. If you plan to use the latest version make sure to add a separate dependency for Jackson.

First is the authorize() method.

private static Credential authorize() throws Exception {    String credentialLocation = System.getProperty("user.dir") + "/GoogleAPIKey/credentials.json";
    
    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JacksonFactory.getDefaultInstance(), new FileReader(credentialLocation));

    List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);

    GoogleAuthorizationCodeFlow googleAuthorizationCodeFlow = new GoogleAuthorizationCodeFlow
            .Builder(GoogleNetHttpTransport.newTrustedTransport(), JacksonFactory.getDefaultInstance(), clientSecrets, scopes)
            .setDataStoreFactory(new FileDataStoreFactory(new File(System.getProperty("user.dir") + "/GoogleAPIKey")))
            .setAccessType("offline")
            .build();

    return new AuthorizationCodeInstalledApp(googleAuthorizationCodeFlow, new LocalServerReceiver()).authorize("user");
}

Basically, what this does is to authorize us and gives us the credentials to get access to our sheets. See how we used the configuration we downloaded earlier. In my case, I created a folder named “GoogleAPIKey” in my project root directory and placed the configuration file named credentials.json in it. Just make sure to add this to your .gitignore file if you don’t want it to be uploaded to your remote repository. To better understand the function, here are the links to the documentation of the primary classes used in this method.

Here now is our method to get the data from our google sheets

public static String[][] getData(String spreadSheetId, String sheetName, String rangeDataToRead) throws Exception {    Sheets sheet = new Sheets(GoogleNetHttpTransport.newTrustedTransport(), JacksonFactory.getDefaultInstance(), authorize());
    
    List<List<Object>> data = sheet.spreadsheets().values()
            .get(spreadSheetId, sheetName + "!" + rangeDataToRead)
            .execute().getValues();
    
    return convertToArray(data);
}

We first created a sheet object. Look at the last argument of our Sheets constructor. We passed our credentials by calling our authorize() method. Next line is for reading the data from our sheet. Notice our get() method. We passed the ID of the spreadsheet where data is coming from. And the second argument is the name of the sheet and the range of cell where data will be read. You’ll notice some string manipulation for the second argument as the api requires our second argument to look like this “SheetName!A2:E”. Since this is for our data driven test automation framework. TestNG data provider, our testing framework of choice, does not accept List<List<Object>> that is why we had to convert it to a 2 dimensional array of String using the method below.

private static String[][] convertToArray(List<List<Object>> data) {
    String[][] array = new String[data.size()][];

    int i = 0;
    for (List<Object> row : data) {
        array[i++] = row.toArray(new String[row.size()]);
    }
    return array;
}

Here is a sample usage of our getData() method.

In case you’re wondering where to get the spreadsheet id. It’s in the url of your spreadsheet.

Since this is just for reading data from google sheets. We won’t be digging into how to update data in google sheets. But I will leave a sample code for you to look into. Try it, look into the documentations and understand how it works.

public static void updateData(String spreadSheetId, String sheetName, String cellLocation, String newValue) throws Exception {
    
if (cellLocation.contains(":")) {
        throw new Exception(String.format("Restricting update to single cell only. You are trying to update cells %s", cellLocation));
}

    Sheets sheets = new Sheets(GoogleNetHttpTransport.newTrustedTransport(), JacksonFactory.getDefaultInstance(), authorize());

    ValueRange updateValue = new ValueRange()
            .setValues(Arrays.asList(Arrays.asList(newValue)));

    sheets.spreadsheets().values()
            .update(spreadSheetId, sheetName + "!" + cellLocation, updateValue)
            .setValueInputOption("RAW")
            .execute();
}

Yey, we’ve just added another utility to your toolkit. Next time, if you don’t want to source your data from excel file, get it from google sheets.

🍻 🍻 🍻

How to Read Data From Google Sheets

Enabling the Google Sheets API

Written by Rommel Malqued