Handling Google Sheet data as json

[hyousef (Hasan Yousef)]

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?

[hyousef (Hasan Yousef)]

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)

set access to Anyone, even anonymous. and deploy.

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

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

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

package main

import (
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"os"
)

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 {
		log.Fatal(err)
	}

	defer resp.Body.Close()

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

	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}

	// 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
[{"ProductId":1,"ProductName":"Helmet"},{"ProductId":2,"ProductName":"Glove"},{"ProductId":3,"ProductName":"Detecttor"}]
id: 1, description: Helmet

Notes:

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