Google App Engine Go 1.11应用程序无法访问Google Spreadsheets

Question

I'm trying to access google spreadsheet via API from the application running on Google App Engine Go 1.11 Standard Environment. Unfortunately, the application cannot read this spreadsheet.

I'm getting next error on Spreadsheets.Values.Get call:

googleapi: Error 403: Request had insufficient authentication scopes., forbidden

sample code

// Sample app showing issue with GAE -> google spreadsheets
package main

import (
    "context"
    "fmt"
    "log"
    "net/http"
    "os"

    "cloud.google.com/go/compute/metadata"
    "golang.org/x/oauth2/google"
    "google.golang.org/api/sheets/v4"
)

func main() {
    http.HandleFunc("/", indexHandler)

    // [START setting_port]
    port := os.Getenv("PORT")
    if port == "" {
        port = "8080"
        log.Printf("Defaulting to port %s
", port)
    }

    // let's check app engine instance scopes
    scopes, _ := metadata.Get("instance/service-accounts/default/scopes")
    log.Printf("[DEBUG] metadata scopes: %s.
", scopes)

    log.Printf("Listening on port %s", port)
    log.Fatal(http.ListenAndServe(fmt.Sprintf(":%s", port), nil))
    // [END setting_port]
}

// indexHandler responds to requests with our greeting.
func indexHandler(w http.ResponseWriter, r *http.Request) {
    ctx := context.Background()
    client, _ := google.DefaultClient(ctx, "https://www.googleapis.com/auth/spreadsheets.readonly")
    srv, err := sheets.New(client)

    // Prints the names and majors of students in a sample spreadsheet:
    // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
    spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
    readRange := "Class Data!A2:E"
    resp, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v
", err)
    }

    if len(resp.Values) == 0 {
        fmt.Fprintf(w, "No data found.
")
    } else {
        fmt.Fprintf(w, "Name, Major:
")
        for _, row := range resp.Values {
            // Print columns A and E, which correspond to indices 0 and 4.
            fmt.Fprintf(w, "%s, %s
", row[0], row[4])
        }
    }

}

steps to reproduce:

1) deploy app: gcloud app deploy
2) open in a browser (you will get 502): gcloud app browse
3) check logs: gcloud app logs read

2018-12-11 21:44:56 default[20181211t134352]  "GET / HTTP/1.1" 502
2018-12-11 21:44:57 default[20181211t134352]  2018/12/11 21:44:57 [DEBUG] metadata scopes: https://www.googleapis.com/auth/appengine.apis
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/cloud-platform
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/cloud_debugger
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/devstorage.full_control
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/logging.write
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/monitoring.write
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/trace.append
2018-12-11 21:44:57 default[20181211t134352]  https://www.googleapis.com/auth/userinfo.email
2018-12-11 21:44:57 default[20181211t134352]  .
2018-12-11 21:44:57 default[20181211t134352]  2018/12/11 21:44:57 Listening on port 8081
2018-12-11 21:44:58 default[20181211t134352]  2018/12/11 21:44:58 Unable to retrieve data from sheet: googleapi: Error 403: Request had insufficient authentication scopes., forbidden

Could someone please help to understand how to fix it?

Sample project: https://github.com/vistrcm/gae-spreadsheet-issue

Answer 1

I've ran into this before as well with App Engine to G Suite integrations. You need to use a service account key. The default one does not suffice (I believe because it does not have a private key, but that might be wrong).

Essentially you will need to upload a key with your code and use that to get a Client (instead of using the default one):

func getOauthClient(serviceAccountKeyPath string) *http.Client {
    ctx := context.Background()
    data, err := ioutil.ReadFile(serviceAccountKeyPath)
    if err != nil {
        log.Fatal(err)
    }
    creds, err := google.CredentialsFromJSON(ctx, data, "https://www.googleapis.com/auth/spreadsheets.readonly")
    if err != nil {
        log.Fatal(err)
    }

    return oauth2.NewClient(ctx, creds.TokenSource)
}