chipkent/GoogleSheets.jl

Julia package for working with Google Sheets

GoogleSheets.jl

Julia package for working with Google Sheets. You can perform expected actions such as adding sheets, removing sheets, reading from sheets, writing to sheets, and formatting sheets.

Key types:

• GoogleSheetsClient
• Spreadsheet
• Sheet
• CellRange
• CellRanges
• CellRangeValues
• UpdateSummary
• CellIndexRange1D
• CellIndexRange2D
• CellFormat
• DataFrame

Key functions:

• sheets_client
• sheet_names
• sheets
• batch_update!
• add_sheet!
• delete_sheet!
• freeze!
• append!
• insert_rows!
• insert_cols!
• delete_rows!
• delete_cols!
• meta
• show
• get
• update!
• clear!
• format!
• format_conditional!
• format_color_gradient!

Install

To install:

pkg] add GoogleSheets
pkg] build GoogleSheets

Use

To use:

1. Create a Google Sheets API token from either the python quick start reference or the developers console.
2. Place the Google Sheets API credentials.json file in ~/.julia/google_sheets/.
3. Connect to Google Sheets using sheets_client.
4. See the scripts directory for examples of using the package.

Example

An example reading data from a Google Sheet. See ./scripts/example_read.jl.

using GoogleSheets

# Example based upon: # https://developers.google.com/sheets/api/quickstart/python

client = sheets_client(AUTH_SCOPE_READONLY)

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
SAMPLE_RANGE_NAME = "Class Data!A2:E"

sheet = Spreadsheet(SAMPLE_SPREADSHEET_ID)
range = CellRange(sheet, SAMPLE_RANGE_NAME)
result = get(client, range)
println("RESULT: $(result)")

if isnothing(result.values)
    println("No data found.")
else
    for row in eachrow(result.values)
        println("ROW: $row")
    end

    println("")
    println("Name, Major:")
    for row in eachrow(result.values)
        # Print columns A and E, which correspond to indices 1 and 5.
        println("ROW: $(row[1]), $(row[5])")
    end
end

An example reading data, writing data, and modifying a Google Sheet. See ./scripts/example_read_write.jl.

using GoogleSheets

# Example based upon: # https://developers.google.com/sheets/api/quickstart/python

client = sheets_client(AUTH_SCOPE_READWRITE)

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = "1pG4OyAdePAelCT2fSBTVJ9lVYo6M-ApuTyeEPz49DOM"
SAMPLE_RANGE_NAME = "Sheet1"

sheet = Spreadsheet(SAMPLE_SPREADSHEET_ID)
range = CellRange(sheet, SAMPLE_RANGE_NAME)
ranges = CellRanges(sheet, ["Sheet1!A1:B9", "Sheet1!B1:B9"])

println()
show(client, sheet)

values = ["0" "1" "2"; "a" "=A1+B1" 33]
println(values)

result = update!(client, range, values)


################################################################################

result = get(client, range)
println("RESULT: $(result)")

if isnothing(result.values)
    println("No data found.")
else
    for row in eachrow(result.values)
        println("ROW: $row")
    end
end

################################################################################

result = get(client, ranges)
println("RESULT: $(result)")

for r in result
    if isnothing(r.values)
        println("No data found.")
    else
        for row in eachrow(r.values)
            println("ROW: $(r.range) $row")
        end
    end
end

################################################################################

try
    delete_sheet!(client, sheet, "test sheet")
    println("Deleted sheet")
catch e
    println("No sheet to delete")
end

add_sheet!(client, sheet, "test sheet")

println()
show(client, sheet, "test sheet")

values = fill(11, 5, 5)
println("VALUES $(typeof(values)) $values")
result = update!(client, CellRange(sheet, "test sheet"), values)

freeze!(client, Sheet(client, sheet, "test sheet"), 2, 3)
append!(client, Sheet(client, sheet, "test sheet"), 1000, 3)

insert_rows!(client, CellIndexRange1D(Sheet(client, sheet, "test sheet"), 2, 3))
insert_cols!(client, CellIndexRange1D(Sheet(client, sheet, "test sheet"), 2, 3))

delete_rows!(client, CellIndexRange1D(Sheet(client, sheet, "test sheet"), 2, 3))
delete_cols!(client, CellIndexRange1D(Sheet(client, sheet, "test sheet"), 2, 3))

clear!(client, CellRange(sheet, "test sheet!B2:C3"))

Documentation

See https://chipkent.github.io/GoogleSheets.jl/dev/.

Pull requests will publish documentation to https://chipkent.github.io/GoogleSheets.jl/previews/PR##.