Reading Google Sheet Content in GO — GOLANG

How does it even work out??Many people would encounter questions as to why such a weird use-case (application).

Well, there’s purpose in doing so (it has some unique benefits).

Let me explain “Why” first,In such an application, the Google Sheet becomes a single place where the language mappings are kept.

Hence, provides single point of control when bringing in changes to the language strings and also maintenance of the language file becomes much easier.

Language strings can be modified during the run time and also update the hosted language files which are generally kept in assets/resources directory without interrupting the running servers.

Adding a new language or removing an existing language can be easily performed, just by adding a new column with new language strings or removing an existing language string column respectively, which allows us to make very little (or in some cases no changes) to the running application.

Now let me explain “How”,Consider we have a google sheet with columns named, key, en (English strings), fr (French strings), and so on for different languages.

“key” is what we use in the real application (code) to map (or identify) a specific language string.

And also consider that we are developing a web application using a JS framework like Angular and we want to make our web application multi-lingual (internationalization), i.

e.

upon some criteria we want to change the language of the content which is displayed in the UI.

In here, we would have a “i18n” folder inside assets/resources folder and have a language file for each of the language in json format (which is the usual practice).

And now let’s connect both together.

What if you can write a simple program which will get the language file which is a google sheet, read all the content in the google sheet, create individual json files for each language available in the google sheet in the assets folder of the web application, so that the content displayed in the web application is updated when a change takes place in the google sheet.

May be by running a cronjob in the hosted machine for every ‘x’ days/hours/minutes.

** Hope you understood what I tried to explain here, if you have any doubts, questions or suggestions please let me know in comments.

Let’s Dive in to Coding.

I am gonna have 2 approaches to address this specific problem, first approach is accessing the google sheet using the published link, downloading it as a .

csv file and then, creating individual json files and the other approach is to make use of the Google Sheets API to access the google sheet and create json files using the content directly.

In this article, I am gonna show the implementation of the first approach, which is accessing the google sheet via the published url and in my next article, I’ll be showing the implementation using the Google Sheets API.

Accessing the Google Sheet via it’s Published URLLet’s do the necessary background work first.

Let’s create a new google sheet and add the relevant data so that your google sheet looks similar to the following google sheet I created:Sample Google SheetThe first column should be the key used for mapping, and the other columns should contain language strings for specific languages, and first row should contain the string used to identify the language or the name that is to be given to the json files that we will be creating eventually.

And now we will publish the google sheet to the web.

Open the google sheet, click on file and select the publish to the web.

And there, select the option comma-separated values (.

csv) and then click publish.

Then, you’ll get a url, save the url for future use.

Steps to Publish the Google Sheet to WebNow let’s create the project’s directory structure.

We will be making use of the logger I created in the following article:Implementing Logger in GO — GOLANGIt is very important to follow best practices when programming, logging is one such best practice used in industry and…medium.

comAnd let's create the following directories and files as shown in the following image:Directory Structure of the ProjectI am not gonna explain a lot about the helper.

go file, since I have already explained about the init function and loggers in this article.

I have added a new struct and a function to the file, I am not gonna explain a lot here, those changes are done to handle and return errors in a better manner and the helper.

go file should look as below:The approach is quite straight forward,Download the published google sheet in .

csv formatExtract the content within the downloaded file to create the json filesLet's create 2 functions in the fileService.

go file to perform the above 2 steps as follows:→ A function to download the google sheet as a .

csv fileThe above function takes in 3 parameters, url, filename and timeout.

The function first sets the timeout for the request and attempts to download the file provided via the url, then there a couple of checks: the status of the response and the content-type provided by the response, if they are of expected values, then we read the body of the response (where the content is available) and write it to the file with permission 644 (644 means, creator can modify/write, while others can only read the file), if need be, change the permissions to 666 so that everyone can read and write.

The function returns an ErrorResponse (A custom struct).

→ A function to read the downloaded content and write in to json filesThe above function receives a single parameter which is the path to the created .

csv file.

First it opens the file and reads all content within, then a for loop is created to loop over the number of languages in the file:for i, lang := range csvFileContent[0][1:] {.

}csvFileContent[0][1:] means take the first (0th index) row from the content and from that row take all records starting from index 1 (2nd record onwards).

Hence, it will create a list of languages (*Remember, our google sheet had key as the first column and, languages start from the second column onwards).

Inside the first loop, we are creating/writing to the json (language) files with permission 644 (*as I mentioned in the Download function's explanation, if need be use permission 666).

And then,mapLn := map[string]string{}a map type is initiated to create a json object in order to write to a json file, the map is populated via another for loop which iterated over the rows in the csv file except the first row which is not row that contains language strings.

for j, row := range csvFileContent[1:] { mapLn[csvFileContent[j+1][0]] = row[i+1]}Finally the json file is written:encodedJSON, _ := json.

Marshal(mapLn)file.

Write(encodedJSON)The created map is converted to a byte array so that it could be written to the created .

json file.

Now our fileService.

go file should look as follows:The main.

go file performs the simplest of tasks, of calling the relevant functions.

And hence, add the following code to the main.

go file which makes the file very small and simple.

The code in that are self explanatory, on top of the file, services and utils packages are imported and the main function first calls the Download function by providing the url and filename, and then after successful download, calls the writeToFile function.

** Before you run please make the necessary changes to the published url and file and/or directory paths mentioned in the code.

Now let's run and see what happens, you will see 2 new files (if you have more columns in the google sheet than me, you'll see more json files being created) in the outputs directory (or the path you have specified in your code).

And the files should contain the proper data in the proper structure.

Example, please take a look at my en.

json file:Newly created en.

json fileWell, it was quite a long article than I expected.

Anyway, I hope I did a good job and this was helpful and you like what you just read.

Please let me know your comments and suggestions.

** The source code can be found at: https://github.

com/BNPrashanth/gsheet-to-json-csv** The idea behind the implementation shown in this article can not only be used to perform the mentioned implementation in a multi-lingual application but also in various other software applications where there is a need to read content from a google sheet and would be really useful in the context of Business Intelligent applications, to get visual data done faster from data available in the form of google sheets and also in Machine Learning/Data Science applications.

** In this code, some of the inputs are hardcoded in order to run the example application provided which is of-course not the best-practice when it comes to any programming language (Need to be taken from environment configurations, which is the better practice).

I will have a few articles on best-practices in GO in future like the preferred directory structure, dependency management and vendoring, reading from environment configurations, etc.

.

. More details

Leave a Reply