The last couple of days I was working on a spring MVC project which had a requirement to integrate google-sheets into it to show the graphical representation of the data.

The solution was simple. It was to use the google oAuth2 and authenticate the user and show the data to them. This worked fine when deployed using jetty and tomcat in my local machine. But when this was deployed to a live/testbed environment the application couldn’t find a browser to popup the authentication dialog to the user. Eventually, the connection would timeout.

After doing some research on the problem I came up with a solution. This was to use a Google service account and create a p12 file with the credentials to that service account and use it to do a server to server authentication and show the details to the user.

Prerequisites

Google Project with Google Sheet API
Google service account
Tomcat/Jetty

Create Spring MVC Project

Next Task is to create a Utility class to get the authenticate and get the data from the google sheet

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;
import org.apache.logging.log4j.LogManager;
import java.io.File;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class SheetsQuickStart {
  private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
  private static final String SERVICE_ACCOUNT_EMAIL = "547581693992-compute@developer.gserviceaccount.com";
  private static final String APPLICATION_NAME = "Google Sheets API";
  private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
  private static final org.apache.logging.log4j.Logger logger = LogManager.getLogger(SheetsQuickStart.class.getName());

  private Credential getCredentials(String KEY_FILE_LOCATION) throws URISyntaxException, IOException, GeneralSecurityException {
URL fileURL = SheetsQuickStart.class.getClassLoader().getResource(KEY_FILE_LOCATION);
  if (fileURL == null) {
    fileURL = (new File(KEY_FILE_LOCATION)).toURI().toURL();
  }
        HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
  return new GoogleCredential.Builder()
    .setTransport(httpTransport)
    .setJsonFactory(JSON_FACTORY)
    .setServiceAccountId(SERVICE_ACCOUNT_EMAIL)
    .setServiceAccountPrivateKeyFromP12File(new File(fileURL.toURI()))
    .setServiceAccountScopes(SCOPES)
    .build();
}
public List getData(String spreadsheetId, String range, String KEY_FILE_LOCATION) throws IOException, GeneralSecurityException, URISyntaxException {
        List Rows = new ArrayList();
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(KEY_FILE_LOCATION))
    .setApplicationName(APPLICATION_NAME)
    .build();
ValueRange response = service.spreadsheets().values()
    .get(spreadsheetId, range)
    .execute();
  List < List < Object >> values = response.getValues();
  try {
    for (int i = 1; i < values.size(); i++) {
      Rows.add(values.get(i));
    }
  } catch (Exception e) {
    logger.error(e.getMessage());
  }
  return Rows;
}
}

The getCredentials(String KEY_FILE_LOCATION) method will create a GoogleCredentials object using the p12 file and the service account that is used to generate the p12 file.

The getData(String spreadsheet, String range, String KEY_FILE_LOCATION) method will use the getCredentials() method to get the user credentials to authenticate the user with the google sheet and return the data to the user.

Next, let’s create a class to be used when retrieving the data

public class SheetDataUtil {
private String businessName;
    private String merchantEmail;
    private String appType;
    private String businessId;
    private String state;
    private String country;
    private String joinedDate;
public SheetDataUtil() {
    }
public SheetDataUtil(String businessName, String merchantEmail, String appType, String businessId, String state, String country, String joinedDate) {
        this.businessName = businessName;
        this.merchantEmail = merchantEmail;
        this.appType = appType;
        this.businessId = businessId;
        this.state = state;
        this.country = country;
        this.joinedDate = joinedDate;
    }
public String getBusinessName() {
        return businessName;
    }
public void setBusinessName(String businessName) {
        this.businessName = businessName;
    }
public String getMerchantEmail() {
        return merchantEmail;
    }
public void setMerchantEmail(String merchantEmail) {
        this.merchantEmail = merchantEmail;
    }
public String getAppType() {
        return appType;
    }
public void setAppType(String appType) {
        this.appType = appType;
    }
public String getBusinessId() {
        return businessId;
    }
public void setBusinessId(String businessId) {
        this.businessId = businessId;
    }
public String getState() {
        return state;
    }
public void setState(String state) {
        this.state = state;
    }
public String getCountry() {
        return country;
    }
public void setCountry(String country) {
        this.country = country;
    }
public String getJoinedDate() {
        return joinedDate;
    }
public void setJoinedDate(String joinedDate) {
        this.joinedDate = joinedDate;
    }
}

Now let’s create the controller class.

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import java.io.IOException;
import java.net.URISyntaxException;
import java.security.GeneralSecurityException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Controller
public class GoogleSheetsController {
private static final Logger logger = LogManager.getLogger(GoogleSheetsController.class.getName());
    private final String GOOGLE_SHEETS = "google_sheets";
    String error;
    private static  AppTypes appTypes = new AppTypes();
    @Value("${google.sheets.spreadsheet.column.count}")
    private int columnNumber;
    @Value("${google.sheets.id}")
    private String spreadSheetId;
    @Value("${google.sheets.spreadsheet}")
    private String range;
    @Value("${google.sheets.p12.file.path}")
    private String KEY_FILE_LOCATION;
    private static final USStateHelper usStatesHelper = new USStateHelper();
    @Autowired
    private UserDetailsHelper userDetailsHelper;
@RequestMapping(value = "/reports/googlesheets/filterDate", method = RequestMethod.GET)
    public String filterData(Model model,
                             @RequestParam(value = "startDate") String startDate,
                             @RequestParam(value = "endDate") String endDate) throws IOException, GeneralSecurityException, ParseException, URISyntaxException {
String[][] sheetData = getSheetData();
        List<SheetDataUtil> filteredSheetData = new ArrayList<>();
        List<States> states = new ArrayList<>();
        try {
                filteredSheetData = addDatatoList(sheetData);
            error = "NONE";
        } catch (Exception e) {
            error = "ERROR";
            logger.error(e.getMessage());
        }
model.addAttribute("apptypeCount", appTypes);
        model.addAttribute("error", "NONE");
        model.addAttribute("sheetData", filteredSheetData);
        model.addAttribute("sheetStates", states);
        model.addAttribute("userDetails", userDetailsHelper.getUserAccount());
        return GOOGLE_SHEETS;
    }
List addDatatoList(String[][] array) throws ParseException {
        List<SheetDataUtil> list = new ArrayList<>();
for (int x = 0; x < array.length; x++) {
                SheetDataUtil sheetDataUtil = addToList(array, x);
                list.add(sheetDataUtil);
        }
        return list;
    }
public String[][] getSheetData() throws IOException, GeneralSecurityException, URISyntaxException {
        SheetsQuickStart sheetsQuickStart = new SheetsQuickStart();
        List rows = sheetsQuickStart.getData(spreadSheetId, range, KEY_FILE_LOCATION);
        for (int y = 0; y < rows.size(); y++) {
            List object = (List) rows.get(y);
            if (object.size() == 0) {
                rows.remove(y);
            }
        }
String[][] array = new String[rows.size()][columnNumber];
        for (int i = 0; i < rows.size(); i++) {
            for (int y = 0; y < columnNumber; y++) {
                List object = (List) rows.get(i);
                array[i][y] = (String) object.get(y);
            }
        }
        return array;
    }
public SheetDataUtil addToList(String[][] array, int x) {
        SheetDataUtil sheetDataUtil = new SheetDataUtil();
        sheetDataUtil.setBusinessName(array[x][0]);
        sheetDataUtil.setMerchantEmail(array[x][1]);
        sheetDataUtil.setAppType(array[x][2]);
        sheetDataUtil.setBusinessId(array[x][3]);
        sheetDataUtil.setState(array[x][4]);
        sheetDataUtil.setCountry(array[x][5]);
        sheetDataUtil.setJoinedDate(array[x][6]);
        return sheetDataUtil;
    }
}

The addDataToList() method will iterate through the array and add the data to a list of type SheetDataUtil

The getSheetData() method will get the data using the getData() method in the SheetsQuickStart class and return the array.

Finally, let’s create the jsp to display the data

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
  <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
    <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
      <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
        <jsp:include page="header_new.jsp" />
        <link rel="stylesheet" type="text/css" href="<c:url value=" /resources/css/googlesheets.css" />">
        <script type="text/javascript" src="https://cdn.jsdelivr.net/jquery/latest/jquery.min.js"></script>
        <script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
        <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js">
        </script>
        <link rel="stylesheet" type="text/css"
          href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />
        <table id="email-template-table"
          class="table table-bordered table-po table-white table-striped table-hover margin-top-20">
          <thead>
            <tr class="header-area">
              <th width="20%">Business Name</th>
              <th width="20%">Merchant Email</th>
              <th width="20%">App Type</th>
              <th width="15%">BusinessId</th>
              <th width="5%">State</th>
              <th width="10%">Country</th>
              <th width="10%">Joined Date</th>
            </tr>
          </thead>
          <tbody>
            <c:forEach items="${sheetData}" var="Rows">
              <tr>
                <td>${Rows.businessName}</td>
                <td>${Rows.merchantEmail}</td>
                <td><span class="${Rows.appType}">${Rows.appType}</span></td>
                <td>
                  <a href="<c:url value=" /businessDetails/${Rows.businessId}" />">${Rows.businessId}</a>
                </td>
                <td>${Rows.state}</td>
                <td>${Rows.country}</td>
                <td>${Rows.joinedDate}</td>
              </tr>
            </c:forEach>
          </tbody>
        </table>
        <script src="<c:url value=" /resources/javascript/googlesheets.js" />"></script>
        </body>

        </html>