Long printout when calling rows on spreadsheet

[justinallenmarsh]

When I run the following command to load a spreadsheet document into memory... I get an incredibly long print out before my console allows me to issue the next command. It seems like it's mapping and printing out the whole spreadsheet when the rows method is called.

session = GoogleDrive::Session.from_service_account_key("config/google_config.json")

ws = session.spreadsheet_by_key("XXXXXXXXXXX").worksheets[0]

rows = ws.rows

It takes a few minutes for a 10mb spreadsheet to finish running the .row command.

Am I doing something wrong or is this likely a bug in the gem?

[y-bonfire]

I checked it with 3.0.10 and it displayed normally.
No problems found

The number of rows or columns might be the problem.

session = GoogleDrive::Session.from_config("xxx.json")
sheets = session.spreadsheet_by_key("xxxx")
worksheet = sheets.worksheets[0]
puts worksheet.rows

The problem might be with the amount or size.
It might be effective to reduce it by 5MB, 2MB, 1MB, etc. and see where the problem lies.

For example, the following CSV test data is useful:
Customers CSV with 500,000 records, 25MB
Customers CSV with 1,000,000 records, 52MB
https://github.com/datablist/sample-csv-files?tab=readme-ov-file

Converting from CSV to Spsh is easy(Copy and paste to deploy)
But without more information, there will be a lot of unnecessary work.

@jmarsh24
1)What version of gem is it?
2)How many rows and columns is it?
3)Is it the fact that it takes a long time to run, or the fact that it displays, or both?
I tried it with a small amount of data, but there was no unnecessary display.
4)If possible, could you please create a CSV file with the problem using the sample above and attach it to the issue? I'll try running it on my computer.

[justinallenmarsh]

1. Gem Version

google_drive_maintained (3.0.10)
  google-apis-drive_v3 (>= 0.63.0)
  google-apis-sheets_v4 (>= 0.42.0)
  googleauth (>= 1.14)
  nokogiri (>= 1.18, < 2.0.0)

2. 20k rows, 19 columns (20mb) https://docs.google.com/spreadsheets/d/1Dbjg00A10Pcbn3_IQnjnSZjWd7JPhjJDEesSfP1gpAE/edit?usp=sharing

3. It seems to take a few minutes just to load the csv file into ruby when running.

session = GoogleDrive::Session.from_service_account_key("config/google_config.json")

ws = session.spreadsheet_by_key("1Dbjg00A10Pcbn3_IQnjnSZjWd7JPhjJDEesSfP1gpAE").worksheets[0]
rows = ws.rows

It displays information while fetching (api requests) but then also displays something that looks like it's mapping over the result. At the end it displays the ruby objects. I have documented what is displayed in the console while running the command.

In total, with this specific file, locally I calculated the method call to take about 3 minutes 10 seconds.

Please let me know if you are able to reproduce the issue! I understand that it's not a light csv file, but I expect that people using this gem and wanting to interact with a csv document via ruby would probably be working with CSV files of this size.

[y-bonfire]

@jmarsh24
Thanks for the information

I cheked this environment

    google_drive_maintained (3.0.10)
      google-apis-drive_v3 (>= 0.63.0)
      google-apis-sheets_v4 (>= 0.42.0)
      googleauth (>= 1.14)
      nokogiri (>= 1.18, < 2.0.0)

checked csv file
10001 rows, 12column, 1.6MB
customers-100000.csv - Zip version - Customers CSV with 100000 records

checked code

session = GoogleDrive::Session.from_config("config.json")
sheets = session.spreadsheet_by_key("xxx")
worksheet = sheets.worksheets[0]
puts worksheet.rows
puts worksheet.rows.length

result
The spreadsheet information was loaded, but no Ruby objects were displayed.
The time it took was 17 seconds.
Unfortunately, I was unable to reproduce the problem.

...
ngallagher@martinez.com
2020-06-27
http://nixon.net/
10001

No problems found
Again, if you can create a CSV file that shows the problem, I'll try it.
Or could you please check that there are no problems with this csv too?

Your post mentions the spreadsheet ID, so be careful about security and information leaks.

[y-bonfire]

It's a bit strange to introduce something I just found through a random search, but this one is written in Go and may be fast.
https://github.com/qax-os/excelize

Why not try searching for other popular libraries and give them a try?
Please share your results if you like.

[justinallenmarsh]

Sorry I realized that I forgot to attach the csv file that I used. I can imagine that it might be helpful for debugging.

https://docs.google.com/spreadsheets/d/1Dbjg00A10Pcbn3_IQnjnSZjWd7JPhjJDEesSfP1gpAE/edit?usp=sharing

I'm running this in a rails application and not completely isolated, but I dont think that this should be an issue.

[y-bonfire]

I extracted the data from a spreadsheet to a CSV file and imported it.
I think the number of rows is 20k.
It processed in 50 seconds.
If you want to process it faster, why not process it locally or split the sheet into multiples?
Or, if you're using Rails, I think you could use a database.
Spreadsheets don't have very good IO performance.
songs_test - songs.csv

[y-bonfire]

Sorry, excelize is Microsoft Excel.
The following seems to be a library for working with Google Spreadsheets.

If you want to use Ruby, I think it would be easier to customize it yourself using google-apis-sheets_v4
https://github.com/googleapis/google-api-ruby-client
google_drive also uses this, so loading speeds won't get any faster than this.

require "google/apis/sheets_v4"
require "googleauth"

# Path to your service account JSON key
SERVICE_ACCOUNT_FILE = "credentials.json"
SPREADSHEET_ID = "YOUR_SPREADSHEET_ID"
RANGE = "Sheet1!A1:D10" # Adjust as needed

# Authorize
scope = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
authorization = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open(SERVICE_ACCOUNT_FILE),
  scope: scope
)

# Initialize Sheets API
service = Google::Apis::SheetsV4::SheetsService.new
service.authorization = authorization

# Fetch values
response = service.get_spreadsheet_values(SPREADSHEET_ID, RANGE)

# Print values
response.values&.each do |row|
  puts row.join("\t")
end

---

Python

• gspread – Simple, well-maintained Google Sheets API wrapper
  https://github.com/burnash/gspread
• pygsheets – Feature-rich, supports batch updates and large sheet handling
  https://github.com/nithinmurali/pygsheets

---

Java

• googleapis/java-sheets – Official Google Sheets API client for Java
  https://github.com/googleapis/java-sheets

---

JavaScript / Node.js

• googleapis – Official Google API client (includes Sheets API)
  https://github.com/googleapis/google-api-nodejs-client
• g-sheets-api – Lightweight wrapper for quick read-only access
  https://github.com/therufa/gsheets

---

C# / .NET

• Google.Apis.Sheets.v4 – Official Google Sheets API client for .NET
  https://github.com/googleapis/google-api-dotnet-client

---

Go

• googleapis/google-api-go-client – Official Go client, Sheets API included
  https://github.com/googleapis/google-api-go-client

---

Rust

• google-apis-rs – Auto-generated Rust clients for Google APIs (includes Sheets)
  https://github.com/Byron/google-apis-rs

---

[justinallenmarsh]

Wow! 50 seconds is a lot faster than what I experience. I wonder if log level info might be causing the issue. The idea was that I could edit the csv by exporting and syncing between my database as the file using this gem.

It has been taking so long to do anything that I resorted to exporting a csv file and manually syncing them, but that's not optimal...

The csv file acts as a way to clean the data so I can sync it with my database.