I am sending data to google sheets api using golang, when headers get updated, sheets api is overwriting the existing headers instead of appending new headers.
Screenshot when everything works fine: Working Sheet screenshot
Screenshot when headers are updated: Sheet screenshot when headers updated
The expected behaviour in second screenshot would be it should created another column as 'surname' and then append data to that column.
The corresponding golang code for this same:
func SendDataToSheet(formResponse *models.FormResponse, integration models.Integration) error {
var sheetIntegration models.SheetIntegration
if err := json.Unmarshal([]byte(integration.Integration), &sheetIntegration); err != nil {
return err
}
decToken, err := utils.Decrypt(integration.Token)
if err != nil {
return err
}
tokenSource := oauth2.Token{}
err = json.Unmarshal([]byte(decToken), &tokenSource)
if err != nil {
return err
}
ctx := context.Background()
if !tokenSource.Valid() {
config := utils.ConfigGoogleSheets()
tokenConfig := config.TokenSource(ctx, &tokenSource)
newTokenSource, _ := tokenConfig.Token()
encToken, err := json.Marshal(newTokenSource)
if err != nil {
return err
}
encTokenString, err := utils.Encrypt(string(encToken))
if err != nil {
return err
}
integration.Token = encTokenString
tokenSource = *newTokenSource
if err := initializers.DB.Save(&integration).Error; err != nil {
return err
}
}
sheetsService, err := sheets.NewService(ctx, option.WithTokenSource(oauth2.StaticTokenSource(&tokenSource)))
if err != nil {
integration.Error = err.Error()
integration.Connected = utils.Bool(false)
if err := initializers.DB.Save(&integration).Error; err != nil {
return err
}
}
dataMap := linkedhashmap.New()
if err := json.Unmarshal([]byte(formResponse.Response), &dataMap); err != nil {
return err
}
headersRangeData := "!A1:Z1"
valuesRangeData := "!A2:Z2"
headers := []interface{}{}
values := []interface{}{}
for _, key := range dataMap.Keys() {
if key != "redirect" && key != "password" {
headers = append(headers, key)
val, _ := dataMap.Get(key)
values = append(values, val)
}
}
valueRange := &sheets.ValueRange{
Values: [][]interface{}{values},
}
headersRange := &sheets.ValueRange{
Values: [][]interface{}{headers},
}
_, err = sheetsService.Spreadsheets.Values.Update(sheetIntegration.SheetId, headersRangeData, headersRange).ValueInputOption("RAW").Do()
if err != nil {
integration.Error = err.Error()
integration.Connected = utils.Bool(false)
if err := initializers.DB.Save(&integration).Error; err != nil {
return err
}
}
_, err = sheetsService.Spreadsheets.Values.Append(sheetIntegration.SheetId, valuesRangeData, valueRange).ValueInputOption("RAW").Do()
if err != nil {
integration.Error = err.Error()
integration.Connected = utils.Bool(false)
if err := initializers.DB.Save(&integration).Error; err != nil {
return err
}
}
return nil
}
Initially data was not being inserted in actual order because map was used, so I have used linkedhashmap which helped to order the elements but still its failing when headers are updated.
Any help would be highly appreciated. Thanks.