How to count cells with data

Question

I'm getting values from range that I specified. But is there a way to count those values?
I'm using this method to get values from the sheets.
spreadsheetId :=""
range2 :=""
valueRenderOption := "FORMATTED_VALUE"

resp, err :=sheetsService.Spreadsheets.Values.Get(spreadsheetId, range2).ValueRenderOption(valueRenderOption).Do()
if err != nil {
    log.Fatal(err)
}

Tanaike · Answer

Modification points:

For sheetsService.Spreadsheets.Values.Get(spreadsheetId, range2).ValueRenderOption(valueRenderOption).Do(), when A1:C1 gives to range2, the returned value is {"majorDimension":"ROWS","range":"A1:C1","values":[["###","###","###"]]}. In this case, resp.Values is 2 dimensional array. By this, len(resp.Values) returns 1. I think that this is the reason of your issue.

When you want to retrieve the total number of cells from sheetsService.Spreadsheets.Values.Get(spreadsheetId, range2).ValueRenderOption(valueRenderOption).Do(), how about the following modification?
Modified script:
resp, err := sheetsService.Spreadsheets.Values.Get(spreadsheetId, range2).ValueRenderOption(valueRenderOption).Do()
if err != nil {
    log.Fatal(err)
}
count := 0
for _, e := range resp.Values {
    count += len(e)
}
fmt.Println(count)

In this case, when A1:C1 is used for range2, 3 is returned.

Note:

For example, when you want to retrieve the total number of cells which are not empty, you can also use the following script.
  resp, err := sheetsService.Spreadsheets.Values.Get(spreadsheetId, range2).ValueRenderOption(valueRenderOption).Do()
  if err != nil {
      log.Fatal(err)
  }
  count := 0
  for _, e := range resp.Values {
      for _, f := range e {
          if f != "" {
              count++
          }
      }
  }
  fmt.Println(count)

In this modified script, it supposes that you have already been able to retrieve the values from Google Spreadsheet using Sheets API. Please be careful this.

Reference:

Method: spreadsheets.values.get

How to count cells with data

One Answer

Modification points:

Modified script:

Note:

Reference:

Add your own answers!

Ask a Question