Google Sheets API empty cells

Getting Help Code Review
1

Hello,
When reading a sheet using Google Sheets API, I’m getting a panic “index out of range” if any cells are empty.

I’m using this package: google.golang.org/api/sheets/v4 which responds with a Values interface (resp.Values).

What is the easiest way to just leave it blank without panicking if it’s blank?

if len(resp.Values) == 0 {
		fmt.Println("No data found.")
	} else {
		var csvOutput string = "ShipDate, BOL, SCAC, PRO, PO, SO, Dest"
		var shipDate, BOL, SCAC, PRO, PO, SO, dest string
		for _, row := range resp.Values {
			// Read values and comma separate into a string.
			shipDate = fmt.Sprintf("%v", row[0])[0:10]
			SCAC = fmt.Sprintf("%v", row[2])
			BOL = fmt.Sprintf("%v", row[3])
			PRO = fmt.Sprintf("%v", row[4])
			PO = fmt.Sprintf("%v", row[5])
			SO = fmt.Sprintf("%v", row[6])
			dest = fmt.Sprintf("%v", row[7])
    }
2

Check the length of the row before you attempt to index into it.

I recommend adding a little helper function:

tryRowSprintf := func(format string, row []interface{}, index int, fallback string) string {
    if index <= 0 || len(row) <= index {
        return fallback
    }
    return fmt.Sprintf(format, row[index])
}

	if len(resp.Values) == 0 {
		fmt.Println("No data found.")
	} else {
		var csvOutput string = "ShipDate, BOL, SCAC, PRO, PO, SO, Dest"
		var shipDate, BOL, SCAC, PRO, PO, SO, dest string
		for _, row := range resp.Values {
			// Read values and comma separate into a string.
			shipDate = tryRowSprintf("%v", row, 0, "0000-00-00")[0:10]
			SCAC = tryRowSprintf("%v", row, 2, "")
			BOL = tryRowSprintf("%v", row, 3, "")
			PRO = tryRowSprintf("%v", row, 4, "")
			PO = tryRowSprintf("%v", row, 5, "")
			SO = tryRowSprintf("%v", row, 6, "")
			dest = tryRowSprintf("%v", row, 7, "")
    }

I would consider this the easiest way because it has few changes to the format of your existing code: There’s a new function but it’s outside of your code’s current “main flow.”

Depending on the code around what you’ve posted, I and/or others may have other suggestions!

2 Likes
3

Thanks Sean! This mostly works, however the shipDate is always 0000-00-00 left like this. The check if index <= 0 means that the first index, [0], always returns fallback.

I tried changing it to if index <= -1 but then I get a panic, “slice bounds out of range [:10] with length 0.”

4

Changing to “if index < 0” and passing “” as the fallback value works well enough. I’ll just have to grab the first 10 digits if they exist later on.

Thanks for your help!

5

My suggestion had a bug in it; this is the correct fix! With the bounds corrected, you should be able to keep using “0000-00-00” (or something else) as the default and keep the [:10].