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

huangapple
go评论85阅读模式
英文:

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

问题

我正在尝试向Google表格中插入或更新数据。我正在使用由golang模块"google.golang.org/api/option"和"google.golang.org/api/sheets/v4"提供的包。

我遇到了以下错误：1.

 got HTTP response code 404 with body: <!DOCTYPE html>\n<html lang=en>\n  <meta charset=utf-8>\n  <meta name=viewport content=\"initial-scale=1, minimum-scale=1, width=device-width\">\n  <title>Error 404 (Not Found)!!1</title>\n  <style>\n    *{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}\n  </style>\n  <a href=//www.google.com/><span id=logo aria-label=Google></span></a>\n  <p><b>404.</b> <ins>That’s an error.</ins>\n  <p>The requested URL <code>/v4/spreadsheets/11dlAKQVB2Hb88fPtg7B9sdPkjmhbNxY8L6H-F1ZY5FI/values/:append?alt=json&insertDataOption=INSERT_ROWS&prettyPrint=false&valueInputOption=USER_ENTERED</code> was not found on this server.  <ins>That’s all we know.</ins>\n"

当我尝试通过动态的sheetname保存时。
2.

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

当我尝试使用sheetId时。

我如何在golang中使用sheetname、sheetid和spreadsheetid插入或更新googlsheet中的值。

这是错误情况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
}

这是错误情况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
}
英文:

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>\n<html lang=en>\n  <meta charset=utf-8>\n  <meta name=viewport content=\"initial-scale=1, minimum-scale=1, width=device-width\">\n  <title>Error 404 (Not Found)!!1</title>\n  <style>\n    *{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}\n  </style>\n  <a href=//www.google.com/><span id=logo aria-label=Google></span></a>\n  <p><b>404.</b> <ins>That’s an error.</ins>\n  <p>The requested URL <code>/v4/spreadsheets/11dlAKQVB2Hb88fPtg7B9sdPkjmhbNxY8L6H-F1ZY5FI/values/:append?alt=json&insertDataOption=INSERT_ROWS&prettyPrint=false&valueInputOption=USER_ENTERED</code> was not found on this server.  <ins>That’s all we know.</ins>\n"

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
}

答案1

得分: 4

我相信你的目标如下。

  • 你想使用Googleapis for golang将records的值附加到电子表格中。
    • 你想在这种情况下使用表格ID。
  • 你已经能够使用Sheets API获取和放置值到Google电子表格中。

在这种情况下，以下修改如何？

不幸的是，表格ID不能直接用于srv.Spreadsheets.Values.Append()。我认为这是你遇到问题的原因。因此，在这种情况下，首先需要将表格ID转换为表格名称。

修改后的脚本：

从：

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
}

到：

sheetid := 0 // 请设置表格ID。
spreadsheetId := "###" // 请设置电子表格ID
records := [][]interface{}{{"a1", "b1", "c1"}} // 这是一个示例值。

// 1. 将表格ID转换为表格名称。
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. 将值附加到表格中。
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
}
  • 运行此脚本时，表格ID将被转换为表格名称。然后，将值附加到表格中。

注意：

  • 如果你可以直接使用表格名称，脚本将如下所示。

      sheetName := "Sheet1" // 请设置表格名称。
  spreadsheetId := "###" // 请设置电子表格ID
  records := [][]interface{}{{"a1", "b1", "c1"}} // 这是一个示例值。

  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
  }

参考资料：

英文:

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:

huangapple
  • 本文由 发表于 2022年4月28日 23:29:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/72046461.html
  • go
  • google-sheets
  • google-sheets-api
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定