Code Monkey home page Code Monkey logo

google-sheets's Introduction

Google Sheets

Go Reference Test Status Coverage Status Lint Status CodeQL Status Go Report Card

Provides an idiomatic way to read and write data from google sheets.

Example Useage

// open the key file for your GCP service account (see below how to create that key file)
jsonServiceAccount, err := ioutil.ReadFile("path\\to\\service_account_key.json")
if err != nil {
  log.Print(err.Error())
  return
}

// spreadsheet id can be taken from the URL
// example URL: https://docs.google.com/spreadsheets/d/c8ACvfAd4X09Hi9mCl4qcBidP635S8z5luk-vGG54N5T/edit#gid=0
// the spreadsheet ID would be "c8ACvfAd4X09Hi9mCl4qcBidP635S8z5lukxvGG54N5T"
sheet, err := gs.OpenSheet(context.Background(), "c8ACvfAd4X09Hi9mCl4qcBidP635S8z5luk-vGG54N5T", "Sheet1", gs.O_CREATE|gs.O_RDWR, jsonServiceAccount)
if err != nil {
  log.Print(err.Error())
  return
}
csvWriter := csv.NewWriter(sheet)
err = csvWriter.WriteAll([][]string{
  {"0", "1"},
  {"2", "3"},
})

csvReader := csv.NewReader(sheet)
csvResult, err := csvReader.ReadAll()
if err != nil {
  log.Print(err.Error())
  return
}
fmt.Printf("results: %v", csvResult)

gs.Remove(context.Background(), gs.SpreadSheetId(), gs.Id(), jsonServiceAccount)

Incomplete Lines

Your google sheet may include non complete lines.

Title A Title B
0 1
2

In this case you should deactivate the field length validation.

csvReader := csv.NewReader(sheet)
csvReader.FieldsPerRecord = -1
csvResult, err := csvReader.ReadAll()
if err != nil {
  log.Print(err.Error())
  return
}
fmt.Printf("results: %v", csvResult)

The output will be as follows

result: [["Title A" "Title B"][0 1] [2]]

Google Sheets AuthN/AuthZ

General

The offical documentation can be found here: https://developers.google.com/sheets/api/guides/authorizing.

Creating the key file

  1. create a gcp service account
  2. after creating the service account, ensure that google project in which the service account resides, is enabled to use the sheet api. You verifiy or enable the API using this url scheme https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=[my gcp project id]
  3. after the service account is created, take the mail address of that account and share your spreadsheet with that mail address
  4. create a json key for your GCP service account

Linting

Project used golangci-lint for linting.

Installation

https://golangci-lint.run/usage/install/

Execution

Run the linting locally by executing

golangci-lint run ./...

in the working directory

Testing

The project contains both unit and integrations tests.

Unit Test Execution

The unit test can be excuted using the default golang commands. To run all test execute the following in the parent folder of the repository.

go test ./...

Integration Test Execution

A credentials file and a google spreadsheet needed as prerequisite for the integration tests. You may use the following launch.json file in VSCode to run the tests.

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "API Wrapper Integration Tests",
            "type": "go",
            "request": "launch",
            "mode": "test",
            "program": "${workspaceFolder}/internal/apiwrapper/apiwrapper_integration_test.go",
            "env": {
                "CREDENTIALS_FILE_PATH": "C:\\Folder\\file-name-352919-3f8fa23b9bba.json",
                "SPREADSHEET_ID": "1yxmv2lTtOtvpkBi-5hSMq86CHFMfYq6kdjfasudfasih"
            },
        },{
            "name": "Sheets Integration Tests",
            "type": "go",
            "request": "launch",
            "mode": "test",
            "program": "${workspaceFolder}/gs/gs_integraton_test.go",
            "env": {
                "CREDENTIALS_FILE_PATH": "C:\\Folder\\file-name-352919-3f8fa23b9bba.json",
                "SPREADSHEET_ID": "1yxmv2lTtOtvpkBi-5hSMq86CHFMfYq6kdjfasudfasih"
            },
        },
    ]
}

google-sheets's People

Contributors

dependabot[bot] avatar github-actions[bot] avatar jo-hoe avatar

Watchers

 avatar

google-sheets's Issues

Linting issues (io/ioutil)

lint: internal/client/httpclient_mock.go#L5
SA1019: "io/ioutil" has been deprecated since Go 1.16: As of Go 1.16, the same functionality is now provided by package io or package os, and those implementations should be preferred in new code. See the specific function documentation for details. (staticcheck)
lint: gs/gs_integraton_test.go#L7
SA1019: "io/ioutil" has been deprecated since Go 1.16: As of Go 1.16, the same functionality is now provided by package io or package os, and those implementations should be preferred in new code. See the specific function documentation for details. (staticcheck)
lint: internal/apiwrapper/apiwrapper_integration_test.go#L6
SA1019: "io/ioutil" has been deprecated since Go 1.16: As of Go 1.16, the same functionality is now provided by package io or package os, and those implementations should be preferred in new code. See the specific function documentation for details. (staticcheck)

Readonly should be default

The sheet is currently opened in read-write mode per default. This behavior should be changed to open the file in read-only mode. This behavior is more closely aligned with a secure-by-default approach.

google-sheets/gs/gs.go

Lines 123 to 127 in dadf262

if hasFlag(flag, O_RDONLY) {
scope = client.ReadOnlyScopes
} else {
scope = client.ReadWriteScopes
}

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.