Marisabel Munoz

I code. I write. I draw. I learn. I love.

Website Content Manager with Google Sheets API and Spring Boot

Well here I am again! I’ve been busy lately with my main project, which I love! And an extra project called Baby #2 🎉. But I finally got to sit down and write a new-bie tutorial / post 😁

I have always wondered how I could load data from Google sheets and use it for a website. Specially, to generate content and update it at will without having to touch the back end code. A while ago, I managed to connect to Google Sheets API, but had no idea what to do with it. That is, until I was far along with my Spring Boot learning through the big project. Then I realized I could make something simple as this! Just for fun and a learning experience.

Here is the profile page demo deployed with Heroku. 🔔

1. The first step is to create a Google Developer’s account, if you haven’t yet.
   1. Go to https://console.cloud.google.com
   2. Under Quick Access, you will find: APIs & Services
   3. Select a Project ➡ NEW PROJECT, and give it a fun name. Hit create.
   4. Navigate to your project. You will see a big dashboard, which I have no clue what 90% of it does.
   5. On the sidebar you will find, APIs & Services
      1. Hit Enable APIs and Services
      2. search & select Google Sheets & enable it!
   6. Once enabled, you need to create your credentials. Go to the top left corner where it says: CREATE CREDENTIALS
      1. I selected:
         • User data
         • Data belonging to a Google user, like their email address or age. User consent required. This will create an OAuth client.
      2. Hit NEXT and add the information needed. Where it says APP NAME, make sure you write down what you pick. As you need this for the code.
      3. For now, I do not select a scope, as that falls inside the 90% of things I still do not understand.
      4. OAuth Client ID
         1. select Web Application and give it a fun name, or serious, or cheesy. As you wish.
         2. Where it says: Authorized redirect URIs, you will edit via IDE in order to obtain your token. I am not sure why it is a different URI every time I switch computers, so it is annoying. 🤷‍♀️ But if you only work with one, it does not matter.
         3. Hit CREATE
      5. download your JSON credentials and rename them as credentials or anything else you wish.

DONE!

Phew, that was Step 1… Now step 2, creating your project!

1. With the https://start.spring.io/ page, create a project with the following dependencies:
   • Spring Web
   • Thymeleaf
   • Lombok
2. Once you have your project, add these as well:
   • com.google.api-client google-api-client 1.35.1
   • com.google.oauth-client google-oauth-client-jetty 1.34.1
   • com.google.apis google-api-services-sheets v4-rev20220606-1.32.1
   • com.google.http-client google-http-client-gson 1.42.0
3. For the classes, you will need the basics:
   • Controller
   • ApiService
   • Model (Optional depending on your content)
     • * On my profile example. I did not use it, as I only had 4 sessions which required lists, so why send them back and forth? I could clean it up and do it on their own services… but this is supposed to be a basic, easy tutorial. So I let it be.
4. You also need the HTML page, call it index or home as you wish.

For the code, please go to the repo.

The only code not in the list, is the iteration through the rows to create a sort of “post” view or lists.

To do this, you need to do the following:

You can create a simple table with just one column and rows. Each row will become a post.

In the service API:

// Initialize sheets

    public void initializeSheetService() throws GeneralSecurityException, IOException {
        sheetService = getSheetService();
    }

// READ ALL POSTS

    public List<List<Object>> readPosts() throws GeneralSecurityException, IOException {
        String range = "tableName!A1:A100";
        ValueRange response = sheetService.spreadsheets().values().get(SHEET_ID, range).execute();
        List<List<Object>> list = response.getValues();
        return list;

    }
    public Object getAllPosts(int n) throws GeneralSecurityException, IOException {
        return readPosts().get(n);
    }

Now, on the controller, we need to create a list, where we are going to add each field of the table. Make sure the table only contains one column. So you need a dedicated table per session you wish to create. You can also do it all in one, but it is harder to maintain. Keep it simple.

You need to add the following to the controller (I might update the repo later on to include this):

    @Autowired
    private SheetsApiService sheets;
    @RequestMapping("/")
    public String indexPagePosts(Model model) throws GeneralSecurityException, IOException {
//      initialize the sheet service from Google
        sheets.initializeSheetService();
//      READ ALL POSTS TABLE
        //  get the size (lenght) of the sheet with data in it
        // (it won't count empty fields)      
        int size = sheets.readPosts().size();
        // initialize the list which will contain all the data      
        List<Object> posts = new ArrayList<>();
        // loop to add the data to the list     
        for (int n = 0; n < size ; n++) {
            String post = String.valueOf(sheets.getAllPosts(n));
        // google returns the data with [ ], so we need to trim that out  
            String convert = post.substring(1, post.length() - 1);
        // add to the list the data   
            posts.add(convert);
        }

        model.addAttribute("posts", posts);

        return "index";
    }

The last step is to do a loop in Thymeleaf:

<div th:each="p : ${posts}">
<p th:utext="${p}"></p>
</div>

That’s it! Have fun! 😁