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.

0

There are 0 best solutions below