I code. I write. I draw. I learn. I love.
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. 🔔
DONE!
Phew, that was Step 1… Now step 2, creating your project!
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! 😁
Leave a Reply