Cover Photo by Manuel Compana

Reading From Sheets During a Batch Job

One would usually use an interactive login to access the Google Sheets on behalf of a user. This approach works perfectly if the user is sitting in front of the computer at the time of access. However, if we want to access Google Sheet information during a batch job (or during the runtime of a Github action, for example), this approach won’t work. An alternative way is to use service accounts.

Setting up the Service Account

The service account panel can be reached by opening IAM & Admin in Google Cloud. After we create the service account, we will get the email of the service account with the following structure <principal-name> @ project-name-12345.iam.gserviceaccount.com. Next, we go to the tab “KEYS”, and generate a JSON key. The key will automatically download to your computer.

Last but not least we share the sheet with the newly created service account

Reading Sheets data using the Java SDK

Now that we have our sheet and the service account is prepared, we can start with the code.

First, we create a Credential object that points to the JSON key we downloaded earlier (the file should be located in src/main/resources)

InputStream in = GoogleAPI.class.getResourceAsStream("/google-sheets-client-secret.json"); 
GoogleCredential credential = GoogleCredential.fromStream(in); List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);
credential = credential.createScoped(scopes);

Now we are able to access the sheet, no login required:

Sheets sheets = Sheets.Builder(          GoogleNetHttpTransport.newTrustedTransport(),           GsonFactory.getDefaultInstance(), credential)          .setApplicationName("foobar")
.build();
BatchGetValuesResponse response = sheets.spreadsheets().values()
.batchGet(SPREADSHEET_ID)
.setRanges(Arrays.asList("A1:A"))
.execute();

--

--

--

DevOps – Linux — Kubernetes –Azure — Java — JS — Apache Camel

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Selenium With BeautifulSoup Tutorial[Python]

Configure WIFI and enable SSH on an SD card with Raspbian

Image Editing with Python

Hack The Box — Networked Walkthrough/Writeup OSCP

IGGalaxy’s Guide to Using MetaMask

GenesisMate Roadmap

An Overview on Odoo ERP Software Solutions

Jensible: Ansible Jenkins Integration

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Aymen Furter

Aymen Furter

DevOps – Linux — Kubernetes –Azure — Java — JS — Apache Camel

More from Medium

CS371p Spring 2022: David Klingler: Final Entry

How to Get Away from Reading-Slump

The Worldview of Meta-Soul

How To Structure Chapters Of Your Novel: 8 Tips For Writing Chapters