Skip to content

google_sheets_api

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History
 
 

google_sheets_api

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
screenshots
screenshots
 
 
src
src
 
 
.gitignore
.gitignore
 
 
README.md
README.md
 
 
pom.xml
pom.xml
 
 

README.md

Info

This directory contains a subset of the https://github.com/eugenp/tutorials intended to be used with the testNg-DataProviders.

The Google Spreadsheet API secret file client_secret.json is loaded from a hidden resource directory .secret under user profile (home) directory - that file must not be checked in. The credentials are valid for one hour (this is probably configurable through Google Developer web interface). In the absence of the secret file the tests would fail.

Usage

The sample test case initializes the sheetsService using the singleton sheetsServiceUtil proxy class:

@Before
public void setup() throws GeneralSecurityException, IOException {
	final String applicationName = "Google Sheets Example";
	sheetsServiceUtil.setApplicationName(applicationName);
final String secretFilePath = Paths.get(System.getProperty("user.home")).resolve(".secret").resolve("client_secret.json").toAbsolutePath().toString();
	sheetsServiceUtil.setSecretFilePath(secretFilePath);
	sheetsService = sheetsServiceUtil.getSheetsService();
}

then reads the spreadsheet data:

@Test
public void test1() throws IOException {
	final String id = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxx";
	final String range = "Users!A2:C";
	System.err.println(
		"Examine spreadsheets in specified id: " + id + " and range: " + range);
	List<List<Object>> values = sheetsService.spreadsheets().values()
		.get(id, range).execute().getValues();
	assertThat(values, notNullValue());
	assertThat(values.size() != 0, is(true));
	System.err.println("Reading " + values.size() + " value rows");
	for (List<Object> row : values) {
		System.err.println("Got: " + row);
	}
}

Data Provider

One can also create a DataProvider method to load the test method parameters in a similar fashion to Excel data provider:

	public List<Object[]> readGoogleSpreadsheet(String spreadsheetId,
			String sheetName) {
		String range = String.format("%s!A2:Z", sheetName);
		List<Object[]> result = new LinkedList<>();

		try {
			ValueRange response = sheetsService.spreadsheets().values()
					.get(spreadsheetId, range).execute();

			List<List<Object>> resultRows = response.getValues();
			assertThat(resultRows, notNullValue());
			assertThat(resultRows.size() != 0, is(true));

			System.err.println("Got " + resultRows.size() + " result rows");
			for (List<Object> resultRow : resultRows) {
				// System.err.println("Got: " + resultRow);
				result.add(resultRow.toArray());
			}
		} catch (IOException e) {
		}
		return result;
	}

See Also

Author

Serguei Kouzmine