How to create a NamgedRange using google sheets API when doing a batch update with Go

Question

I recently started writing a program to export CSV and SQL to google sheets. And in some scenarios I need to create a NamedRange while creating the sheet and/or updating it. The google official documentation is kinda confusion and not very helpful for me. Can anyone please show me an example code or point me in the right direction?

Right now I have something along these lines. This is just sample code to show one of the scenarios.

    func writeSS(ssid string, content [][]interface{}) {

    ctx := context.Background()

    b, err := ioutil.ReadFile("./credentials/client_secret.json")
    if err != nil {
        log.Fatalf("Unable to read client secret file: %v", err)
    }

    config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets.readonly")
    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)
    }

    spreadsheetId := ssid

    rangeData := "Sheet name!A1:A6"

    rb := &sheets.BatchUpdateValuesRequest{
        ValueInputOption: "USER_ENTERED",
    }

    rb.Data = append(rb.Data, &sheets.ValueRange{
        Range:  rangeData,
        Values: content,
    })
    _, err = srv.Spreadsheets.Values.BatchUpdate(spreadsheetId, rb).Context(ctx).Do() //Check this again
    // _, err = srv.Spreadsheets.Values.Update(spreadsheetId, writeRange, &vr).ValueInputOption("USER_ENTERED").Do()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Done.")
}

What is the problem? Try to lay it out in the form - I want to do X, I try Y, instead I get Z. Is there an error you are getting? Which documentation you are trying to follow? — jabbson, Nov 01 '21 at 00:59
There is no problem, I'm simply trying to get a sample code on how to create a RangedName. — VandrerenErik, Nov 01 '21 at 01:22

score 1 · Accepted Answer · answered Nov 01 '21 at 02:14

I believe your goal is as follows.

You want to create a named range using googleapis with golang.
You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

When I saw your script, the method of spreadsheets.values.batchUpdate of Sheets API is used. When you want to create the named range in the existing Google Spreadsheet, please use the method of spreadsheets.batchUpdate.
In your script, you are trying to put the values to the cells using the scope of https://www.googleapis.com/auth/spreadsheets.readonly. I think that an error related to the scopes occurs. In this case, please use the scope of https://www.googleapis.com/auth/spreadsheets.

When these points are reflected in your script, it becomes as follows.

Modified script:

config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
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)
}

spreadsheetId := "###" // Please set your Spreadsheet ID.
sheetId := 1234567890 // Please set your sheet ID.
nameOfNamedRange := "sampleNamedRange1" // Please set the name of the named range.

req := sheets.Request{
    AddNamedRange: &sheets.AddNamedRangeRequest{
        NamedRange: &sheets.NamedRange{
            Range: &sheets.GridRange{
                SheetId:          int64(sheetId),
                StartRowIndex:    1,
                EndRowIndex:      3,
                StartColumnIndex: 1,
                EndColumnIndex:   3,
            },
            Name: nameOfNamedRange,
        },
    },
}
requestBody := &sheets.BatchUpdateSpreadsheetRequest{
    Requests: []*sheets.Request{&req},
}
resp, err := srv.Spreadsheets.BatchUpdate(spreadsheetId, requestBody).Do()
if err != nil {
    log.Fatal(err)
}
fmt.Print(resp)

In this sample script, the gridrange of StartRowIndex: 1, EndRowIndex: 3, StartColumnIndex: 1, EndColumnIndex: 3, means the cells "B2:C3".
When this script is run, the named range of nameOfNamedRange is created with the range of "B2:C3" of sheetId in the Google Spreadsheet of spreadsheetId.

Note:

From your showing script, unfortunately, I cannot know the filename of the file including the access token and refresh token. If the filename is token.json used in the Quickstart, before you run the modified script, please delete the file. And, please reauthorize the scopes. Please be careful about this.

How to create a NamgedRange using google sheets API when doing a batch update with Go

1 Answers1

Modification points:

Modified script:

Note:

References: