How to insert data inside googlesheet using golang with spreadsheetid , spreadsheetname, sheetid and values

Question

I am trying to insert or update data into google spread sheet. I am using package subsribed by golang modules "google.golang.org/api/option" and "google.golang.org/api/sheets/v4"

I am getting errors like:- 1.

 got HTTP response code 404 with body: <!DOCTYPE html>
<html lang=en>
  <meta charset=utf-8>
  <meta name=viewport content=\"initial-scale=1, minimum-scale=1, width=device-width\">
  <title>Error 404 (Not Found)!!1</title>
  <style>
    *{margin:0;padding:0}html,code{font:15px/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background:url(//www.google.com/images/errors/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background:url(//www.google.com/images/branding/googlelogo/1x/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat 0% 0%/100% 100%;-moz-border-image:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) 0}}@media only screen and (-webkit-min-device-pixel-ratio:2){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat;-webkit-background-size:100% 100%}}#logo{display:inline-block;height:54px;width:150px}
  </style>
  <a href=//www.google.com/><span id=logo aria-label=Google></span></a>
  <p><b>404.</b> <ins>That’s an error.</ins>
  <p>The requested URL <code>/v4/spreadsheets/11dlAKQVB2Hb88fPtg7B9sdPkjmhbNxY8L6H-F1ZY5FI/values/:append?alt=json&amp;insertDataOption=INSERT_ROWS&amp;prettyPrint=false&amp;valueInputOption=USER_ENTERED</code> was not found on this server.  <ins>That’s all we know.</ins>
"

When I try to save through dynamic sheetname. 2.

Error while updating records error: googleapi: Error 400: Unable to parse range: 655963475, badRequest"}

While trying to use sheetId

How can I insert or update values into googlsheet using sheetname, sheetid and spreadsheetid in golang.

Here is my code for error case 2:-

func SaveRecords(reqId string, credentials, tokenConfig []byte, spreadsheetId, sheetname string, records [][]interface{}) error {
    ctx := context.Background()

    config, err := google.ConfigFromJSON(credentials, "https://www.googleapis.com/auth/spreadsheets")
    if err != nil {
        return err
    }
    token := oauth2.Token{}
    json.Unmarshal(tokenConfig, &token)

    client := config.Client(ctx, &token)
    srv, err := sheets.NewService(ctx, option.WithHTTPClient(client))
    if err != nil {
        log.Print(err)
return err
    }

    valueInputOption := "USER_ENTERED"
    insertDataOption := "INSERT_ROWS"
    rb := &sheets.ValueRange{
        Values: records,
    }
    response, err := srv.Spreadsheets.Values.Append(spreadsheetId, sheetname, rb).ValueInputOption(valueInputOption).InsertDataOption(insertDataOption).Context(ctx).Do()
    if err != nil || response.HTTPStatusCode != 200 {
        
        return err
    }
    return nil
}

here is my code for error case 1:-

func SaveRecords(reqId string, credentials, tokenConfig []byte, spreadsheetId, sheetname string,sheetid string, records [][]interface{}) error {
    ctx := context.Background()

    config, err := google.ConfigFromJSON(credentials, "https://www.googleapis.com/auth/spreadsheets")
    if err != nil {
        return err
    }
    token := oauth2.Token{}
    json.Unmarshal(tokenConfig, &token)

    client := config.Client(ctx, &token)
    srv, err := sheets.NewService(ctx, option.WithHTTPClient(client))
    if err != nil {
        log.Print(err)
return err
    }

    valueInputOption := "USER_ENTERED"
    insertDataOption := "INSERT_ROWS"
    rb := &sheets.ValueRange{
        Values: records,
    }
    response, err := srv.Spreadsheets.Values.Append(spreadsheetId, sheetid, rb).ValueInputOption(valueInputOption).InsertDataOption(insertDataOption).Context(ctx).Do()
    if err != nil || response.HTTPStatusCode != 200 {
        
        return err
    }
    return nil
}

Tanaike · Accepted Answer

I believe your goal is as follows.

You want to append the value of records to the Spreadsheet using googleapis for golang.
- You want to use the sheet ID for this situation.
You have already been able to get and put values to Google Spreadsheet using Sheets API.

In this case, how about the following modification?

Unfortunately, the sheet ID cannot be directly used to srv.Spreadsheets.Values.Append(). I think that this is the reason of your issue. So, in this case, first, it is required to convert the sheet ID to the sheet name.

Modified script:

From:

valueInputOption := "USER_ENTERED"
insertDataOption := "INSERT_ROWS"
rb := &sheets.ValueRange{
    Values: records,
}
response, err := srv.Spreadsheets.Values.Append(spreadsheetId, sheetname, rb).ValueInputOption(valueInputOption).InsertDataOption(insertDataOption).Context(ctx).Do()
if err != nil || response.HTTPStatusCode != 200 {
    
    return err
}

To:

sheetid := 0 // Please set sheet ID.
spreadsheetId := "###" // Please set Spreadsheet ID
records := [][]interface{}{{"a1", "b1", "c1"}} // This is a sample value.

// 1. Convert sheet ID to sheet name.
response1, err := srv.Spreadsheets.Get(spreadsheetId).Fields("sheets(properties(sheetId,title))").Do()
if err != nil || response1.HTTPStatusCode != 200 {
    return err
}
sheetName := ""
for _, v := range response1.Sheets {
    prop := v.Properties
    sheetID := prop.SheetId
    if sheetID == int64(sheetid) {
        sheetName = prop.Title
        break
    }
}

// 2. Append value to the sheet.
valueInputOption := "USER_ENTERED"
insertDataOption := "INSERT_ROWS"
rb := &sheets.ValueRange{
    Values: records,
}
response2, err := srv.Spreadsheets.Values.Append(spreadsheetId, sheetName, rb).ValueInputOption(valueInputOption).InsertDataOption(insertDataOption).Context(ctx).Do()
if err != nil || response2.HTTPStatusCode != 200 {
    return err
}

When this script is run, the sheet ID is converted to the sheet name. And, the value is appended to the sheet.

Note:

If you can directly use the sheet name, the script becomes as follows.

  sheetName := "Sheet1" // Please set sheet name.
  spreadsheetId := "###" // Please set Spreadsheet ID
  records := [][]interface{}{{"a1", "b1", "c1"}} // This is a sample value.

  valueInputOption := "USER_ENTERED"
  insertDataOption := "INSERT_ROWS"
  rb := &sheets.ValueRange{
      Values: records,
  }
  response2, err := srv.Spreadsheets.Values.Append(spreadsheetId, sheetName, rb).ValueInputOption(valueInputOption).InsertDataOption(insertDataOption).Context(ctx).Do()
  if err != nil || response2.HTTPStatusCode != 200 {
      return err
  }

References:

Method: spreadsheets.get
Method: spreadsheets.values.append

How to insert data inside googlesheet using golang with spreadsheetid , spreadsheetname, sheetid and values

Tags:

go

google-sheets

google-sheets-api

Sri Chak

1 Answers

Modified script:

From:

To:

Note:

References:

Tanaike

Recent Activity

Donate For Us

How to insert data inside googlesheet using golang with spreadsheetid , spreadsheetname, sheetid and values

Tags:

go

google-sheets

google-sheets-api

Sri Chak

1 Answers

Modified script:

From:

To:

Note:

References:

Tanaike

Related questions

Recent Activity

Donate For Us