go-google-sheet.go

go-google-sheet.go

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
	"google.golang.org/api/option"
	"google.golang.org/api/sheets/v4"
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"strings"
	"time"
)

var templateSheetId = int64(0)
var templateSheetName = "Template"
var spreadsheetID = "xxx"

var modulePicMap = map[string]string{
	"App/Feature Detail":           "Hien Do",
	"App store":                    "Hien Do",
	"PLB content alert seller":     "Phong Do",
	"Block button":                 "Hien Do",
	"Block Variant picker":         "Linh Mai",
	"Block Product Gallery":        "Phong Do",
	"Export product":               "Linh Mai",
	"Import product sbase":         "Linh Mai",
	"Library detail update":        "Linh Mai",
	"NE - Popup":                   "Phong Do",
	"NE - Products":                "Hien Do",
	"NE- Collection detail":        "Linh Mai",
	"NE- Collection list":          "Hien Do",
	"NE- Featured collection":      "Linh Mai",
	"NE- Variable":                 "Linh Mai",
	"Partner dashboard affiliate":  "Phong Do",
	"Product Card":                 "Phong Do",
	"Save web as template":         "Hien Do",
	"Social proof list in hive":    "Hien Do",
	"Sticky add to card":           "Hien Do",
	"Template store":               "Linh Mai",
	"Update Blog New Ecom":         "Hien Do",
	"Web builder - Product Detail": "Hien Do",
	"Blogs":                        "Phong Do",
	"Block Breadcrumb":             "Phong Do",
	"NE - Product search":          "Linh Mai",
}

type Item struct {
	ID             int    `json:"id"`
	Code           string `json:"code"`
	Name           string `json:"name"`
	ModuleName     string `json:"module_name"`
	DevResult      string `json:"dev_result"`
	ProdtestResult string `json:"prodtest_result"`
	ProdResult     string `json:"prod_result"`
}

// Retrieve a token, saves the token, then returns the generated client.
func getClient(config *oauth2.Config) *http.Client {
	// The file token.json stores the user's access and refresh tokens, and is
	// created automatically when the authorization flow completes for the first
	// time.
	tokFile := "token.json"
	tok, err := tokenFromFile(tokFile)
	if err != nil {
		tok = getTokenFromWeb(config)
		saveToken(tokFile, tok)
	}
	return config.Client(context.Background(), tok)
}

// Request a token from the web, then returns the retrieved token.
func getTokenFromWeb(config *oauth2.Config) *oauth2.Token {
	authURL := config.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
	fmt.Printf("Go to the following link in your browser then type the "+
		"authorization code: \n%v\n", authURL)

	var authCode string
	if _, err := fmt.Scan(&authCode); err != nil {
		log.Fatalf("Unable to read authorization code: %v", err)
	}

	tok, err := config.Exchange(context.TODO(), authCode)
	if err != nil {
		log.Fatalf("Unable to retrieve token from web: %v", err)
	}
	return tok
}

// Retrieves a token from a local file.
func tokenFromFile(file string) (*oauth2.Token, error) {
	f, err := os.Open(file)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	tok := &oauth2.Token{}
	err = json.NewDecoder(f).Decode(tok)
	return tok, err
}

// Saves a token to a file path.
func saveToken(path string, token *oauth2.Token) {
	fmt.Printf("Saving credential file to: %s\n", path)
	f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_TRUNC, 0600)
	if err != nil {
		log.Fatalf("Unable to cache oauth token: %v", err)
	}
	defer f.Close()
	json.NewEncoder(f).Encode(token)
}

func main() {
	ctx := context.Background()
	b, err := os.ReadFile("credentials.json")
	if err != nil {
		log.Fatalf("Unable to read client secret file: %v", err)
	}

	// If modifying these scopes, delete your previously saved token.json.
	config, err := google.ConfigFromJSON(b, sheets.SpreadsheetsScope)
	if err != nil {
		log.Fatalf("Unable to parse client secret file to config: %v", err)
	}
	client := getClient(config)

	srv, err := sheets.NewService(ctx, option.WithHTTPClient(client))
	if err != nil {
		log.Fatalf("Unable to retrieve Sheets client: %v", err)
	}

	// Prints the names and majors of students in a sample spreadsheet:
	// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
	//spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
	//readRange := "Case monitor!A1:C"
	//resp, err := srv.Spreadsheets.Values.Get(spreadsheetID, readRange).Do()
	//if err != nil {
	//	log.Fatalf("Unable to retrieve data from sheet: %v", err)
	//}
	//
	//if len(resp.Values) == 0 {
	//	fmt.Println("No data found.")
	//} else {
	//	fmt.Println("Name, Major:")
	//	for _, row := range resp.Values {
	//		// Print columns A and E, which correspond to indices 0 and 4.
	//		fmt.Printf("%s, %s\n", row[0], row[1])
	//	}
	//}

	todaySheetName := time.Now().Format("02/01")
	fmt.Printf("Today sheet name: " + todaySheetName)
	templateSheetId = getSheetId(srv, templateSheetName)

	todaySheetId := getSheetId(srv, todaySheetName)
	if todaySheetId == 0 {
		duplicateSheet(srv, todaySheetName)
	}
	//fmt.Println("New template id: ", newTemplateId)

	filePath := "db.json"
	data, err := ioutil.ReadFile(filePath)
	if err != nil {
		fmt.Println("Error reading file:", err)
		return
	}

	// Unmarshal JSON data into a slice of Item structs
	var items []Item
	if err := json.Unmarshal(data, &items); err != nil {
		fmt.Println("Error unmarshaling JSON:", err)
		return
	}

	updateData := sheets.ValueRange{
		Values: make([][]interface{}, 0),
	}

	for i := 0; i < len(items); i++ {
		item := items[i]
		caseValue := fmt.Sprintf("=HYPERLINK(\"%s\",\"[%s] %s - %s\")", fmt.Sprintf("https://test-hub.ocg.to/admin/th/case/%v/change/", item.ID), item.ModuleName, item.Code, strings.Replace(item.Name, "\"", "'", -1))
		picValue := modulePicMap[item.ModuleName]

		values := []interface{}{
			caseValue,
			picValue,
			"Not started",
			item.DevResult,
			item.ProdtestResult,
			item.ProdResult,
		}
		updateData.Values = append(updateData.Values, values)
	}

	// Specify the range to update, e.g., "Sheet1!A2" for row 2 in Sheet1.
	rangeToUpdate := fmt.Sprintf("%s!A4:F%v", todaySheetName, 4+len(items))

	err = updateContent(srv, todaySheetName, updateData, rangeToUpdate)
	if err != nil {
		fmt.Println("Err when update row content: ", err.Error())
	}

}

func duplicateSheet(srv *sheets.Service, newSheetName string) int64 {
	// Get the current sheet order of the source sheet.
	sheet, err := srv.Spreadsheets.Get(spreadsheetID).Ranges("Template!A1:C").Fields("sheets.properties.index").Do()
	if err != nil {
		fmt.Println("Err when get spreadsheet: ", err.Error())
		return 0
	}

	// Calculate the insert index for the new sheet (next to the source sheet).
	insertIndex := sheet.Sheets[0].Properties.Index + 1

	// Create a request to duplicate the specified sheet.
	request := sheets.Request{
		DuplicateSheet: &sheets.DuplicateSheetRequest{
			SourceSheetId:    templateSheetId,
			NewSheetName:     newSheetName,
			InsertSheetIndex: insertIndex,
		},
	}

	// Batch update the spreadsheet to duplicate the sheet.
	batchUpdateRequest := sheets.BatchUpdateSpreadsheetRequest{
		Requests: []*sheets.Request{&request},
	}

	_, err = srv.Spreadsheets.BatchUpdate(spreadsheetID, &batchUpdateRequest).Do()
	if err != nil {
		log.Fatalf("Unable to duplicate sheet: %v", err)
	}

	fmt.Println("Sheet duplicated successfully.")

	return insertIndex
}

func getSheetId(srv *sheets.Service, sheetName string) int64 {
	resp, err := srv.Spreadsheets.Get(spreadsheetID).Fields("sheets.properties").Do()
	if err != nil {
		log.Fatalf("Unable to retrieve spreadsheet: %v", err)
	}

	// Loop through the sheets to find the sheet with the specified name and get its ID.
	var sheetID int64
	for _, sheet := range resp.Sheets {
		if sheet.Properties.Title == sheetName {
			sheetID = sheet.Properties.SheetId
			break
		}
	}

	if sheetID != 0 {
		fmt.Printf("Sheet '%s' has ID: %d\n", sheetName, sheetID)
	} else {
		fmt.Printf("Sheet '%s' not found in the spreadsheet.\n", sheetName)
	}

	return sheetID
}

func updateContent(srv *sheets.Service, sheetName string, data sheets.ValueRange, rangeToUpdate string) error {
	// Create a ValueRange to update the row's values.

	_, err := srv.Spreadsheets.Values.Update(spreadsheetID, rangeToUpdate, &data).ValueInputOption("USER_ENTERED").Do()
	if err != nil {
		return err
	}

	fmt.Printf("Row %d updated successfully.\n", rangeToUpdate)
	return nil
}