Google Sheets integration: «Error while creating spreadsheet»

Third party integrations
1

I just tried configuring the Google Sheets integration.

Everything goes fine until, when I confirm the “Link account to Home Assistant?” message, I receive the error message:

Error while creating spreadsheet, see error log for details

This is what I found in the error log:

Logger: homeassistant.components.google_sheets.config_flow
Source: components/google_sheets/config_flow.py:88
Integration: Google Sheets (documentation, issues)
First occurred: 11:05:17 AM (1 occurrences)
Last logged: 11:05:17 AM

Error creating spreadsheet: {'code': 403, 'message': 'Google Sheets API has not been used in project 1XXXXXXXXXXX9 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=1XXXXXXXXXXX9 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.', 'status': 'PERMISSION_DENIED', 'details': [{'@type': 'type.googleapis.com/google.rpc.Help', 'links': [{'description': 'Google developers console API activation', 'url': 'https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=1XXXXXXXXXXX9'}]}, {'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'SERVICE_DISABLED', 'domain': 'googleapis.com', 'metadata': {'service': 'sheets.googleapis.com', 'consumer': 'projects/1XXXXXXXXXXX9'}}]}

If I access the URL above, I see that the Google Sheets API is enabled, so I supposed that, as they say, «If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.»

Unfortunately, I waited 5, 15, 30, 45, 60, 120 minutes and retried: same error.

I even tried removing the Application Credentials, as suggested in the Troubleshooting section of this integration’s docs page, to no avail.

Is it a problem with this new integration, or is there anything else I could try?

2

Cant help as I have exactly the same issue :slight_smile:

What are we doing wrong?

3

I just linked the account with no problems at all.

did you guys enable google drive api and sheets api ?

but i still need to find out how to push the data, i can’t get it done

4

did you guys enable google drive api and sheets api ?

Yes, using the URL that I find in the error message, it brings me directly to the Google Sheets API detail page for the account being used, and it shows clearly:

Status
Enabled

5

At least I know it’s not just me! :grinning_face_with_smiling_eyes:

Question: do you by any chance also have Google Assistant integration enabled? I’m supposing there could be some permission/credentials conflict, maybe…

6

I may have solved it, but I really don’t understand the rationale behind it.

I went on:

https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=<your project here>

and I **re-**enabled the Google Sheets API.

Magic happened, and the sheet was created.

I don’t know if that’s what solved it, or just the propagation finally occurred (4h40’ after the initial config), but now it works.

3 Likes
7

yes i have 3 integrations enabled.
Google Assistant, Sheets, and assistant relay.

Yes you need to enable sheets and google drive, and it will work.

Do you know perhaps how an automation works to send the data ?
I can;t get it work.

regards

8

Haven’t tried it yet, sorry. If I get to try it soon, I will let you know.

9

thanks in advance

10

Well thanks that worked, I enabled google sheets api and it works. Just checked through the docs and it says to enable google drive but nothing about enabling google sheets, unless i have missed something.

1 Like
11

Yeah you did not mis it, not documented in step 18.

This guy showed it, thats how i did now.

but need to figure out how to send the data, as it’s not fully documented

12

I made it work.

Just for testing, I added a button card with this config:

type: button
tap_action:
  action: call-service
  service: google_sheets.append_sheet
  data:
    worksheet: Sheet1
    config_entry: fd197af22d9a7b6183495f68395a96c6
    data:
      foo: bar
      this: that
name: Google Sheets Append Test
icon: mdi:test-tube

I had a hard time finding what config_entry should have been but, in the end, if you go in the visual editor for the card, in the Integration* field you select Google Sheets, and it will populate it for you. Definitely, this should be explained in the docs.

Under the second data, foo and this are the column names, bar and that are the values.

2 Likes
13

Ok i did try to write data, and it works.

but:
Visual editor is not supported for this configuration

If i do change it just without template it does not write the value.
Also before using the worksheet name, just create a name in your sheet first otherwise you get an error.

This works:

service: google_sheets.append_sheet
data:
  config_entry: XXXXXXXXXXXXXXXXX
  worksheet: LivingroomTemp
  data:
    LivingroomTemp: "{{(states('sensor.livingroomdev_temperature')|float)|round(2)}}"
14

Based on the above I created an automation

alias: send rainfall to sheets
description: ""
trigger:
  - platform: time
    at: "23:59:59"
condition: []
action:
  - service: google_sheets.append_sheet
    data:
      config_entry: 44d99a08a2ad9abxxxx34868f4cfe8f7
      data:
        Rainfall: "{{(states('sensor.rainfall_today')|float)|round(2)}}"
mode: single

It appears to work, lets see how it goes tonight

1 Like
15

Did it work for you? For me, it writes the content of the template into the column.

EDIT: I stand corrected, there was a little typo on my side. Thanks!

16

Yes it worked for me, recorded yesterdays rainfall at midnight. Put rainfall in column a and time in column b. Now added another line to do temperature at midnight.

alias: send rainfall to sheets
description: ""
trigger:
  - platform: time
    at: "23:59:59"
condition: []
action:
  - service: google_sheets.append_sheet
    data:
      config_entry: 44d99a08a2ad9ab6e9a34868f4cfe8f7
      data:
        Rainfall: "{{(states('sensor.rainfall_today')|float)|round(2)}}"
        Temperature: "{{(states('sensor.outside_temp_ds18b20_temperature')|float)|round(2)}}"
mode: single