Handling Google Sheet data as json

I’ve the below sample data at Google Sheets, is there a way to combine both Google Apps Script with Go Lang, so that I can work the sheet data as Json in my GO app?

enter image description here

I got the answer, as below:

1- At Google drive, create new script sheet and write a code as below:

function doGet(){
  // Open Google Sheet using ID
  var ss = SpreadsheetApp.openById("1eLDCzOGyctqXgQmC5qULZ4thcNKsIOdKLJoYc762CTk");
  var sheet = ss.getSheetByName("Master");
  // Read all data rows from Google Sheet
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  // Converts data rows in json format
  const result = JSON.stringify(values.map(([a,b]) => ({ProductId: a,ProductName:b,})));
  // Returns Result
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

Publish the script, youu may be asked for authintiction the first time you publish it, so you need to accept them (note: every time you make changes, you have to publish it under another version number)

enter image description here

set access to Anyone, even anonymous. and deploy.

enter image description here

Sign in with your google account. Go with unsafe options below.

enter image description here

Allow permission to get the URL of your web-app API.

enter image description here

2- In Go Lang, write the below code, using the link generated from the publish step before:

package main

import (

type Product struct {
	ProductId   uint   `json:"ProductId"`
	ProductName string `json:"ProductName"`

func main() {
	// Read the returned response
	url := "https://script.googleusercontent.com/macros/echo?user_content_key=WBSJPDNSN6X1FCYeXsR6TDaDval0vdvmSoMmXFhGbt5sfK0ia80Dp7kPD27GLpZbYz8vrwfDiUecI2oGMjEtgfL5o8Da25T1m5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnGb6k9xaGtOX6M1tIiG811CRpk9nXl8ZKS7UJTno1dvQXMe1kqfAj8WxsSkLor-EqzOmbnRGq-tk&lib=M0B6GXYh0EOYMkP7qr1Xy9xw8GuJxFqGH"
	resp, err := http.Get(url)
	if err != nil {

	defer resp.Body.Close()

	// Read the body of the response
	htmlData, err := ioutil.ReadAll(resp.Body)

	if err != nil {

	// print out
	fmt.Println(string(htmlData)) // The data is returned as []byte, so string required to display it correctly

	// Unmarshall the returned []byte into json
	var products []Product
	json.Unmarshal([]byte(htmlData), &products)
	fmt.Printf("id: %v, description: %s", products[0].ProductId, products[0].ProductName)

Run the script above, you’ll get:

PS D:\> go run gsheet.go
id: 1, description: Helmet


In the go code, you need to ensure the below:
1- The unmarshal target struct must match the data
2- The fields types should be matching, for example ProductID should be uint it will fail if defined as string
3- The json OP got in output should match the tags used, for example, using json:"id" instead of json:"ProductId" for ProductId will fail

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.