Google Sheets - Loading...

[XAGSolutions]

We are running into an issue with the module displaying data.

{ code: 'MODULE_NOT_FOUND', requireStack: [ '/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_helper.js', '/Users/admin/MagicMirror/js/app.js', '/Users/admin/MagicMirror/js/electron.js', '/Users/admin/MagicMirror/node_modules/electron/dist/Electron.app/Contents/Resources/default_app.asar/main.js', undefined ]

This part was resolved for the black screen with a previous issue report.

But after we got Magic Mirror to launch again we are only seeing a "Loading..." where the module is supposed to be. We have rebooted, run all updates rebuilt using classic mode etc etc.

This is what terminal is spitting out:

[23.03.2022 17:29.20.187] [ERROR] Whoops! There was an uncaught exception... [23.03.2022 17:29.20.189] [ERROR] SyntaxError: Unexpected token < in JSON at position 1 at JSON.parse () at Request._callback (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_helper.js:47:27) at Request.self.callback (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:185:22) at Request.emit (node:events:390:28) at Request. (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:1154:10) at Request.emit (node:events:390:28) at IncomingMessage. (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:1076:12) at Object.onceWrapper (node:events:509:28) at IncomingMessage.emit (node:events:402:35) at endReadableNT (node:internal/streams/readable:1343:12)

Any help would be greatly appreciated.

[YeetGott]

same problem

[adriansantaella]

Debugging the code in Google Sheets Apps Script shows an error in the doGet() function.

TypeError: Cannot read property 'parameters' of undefined doGet @ Code.gs:3

[ryan-d-williams]

@XAGSolutions @YeetGott @redshiftgraphics

This is usually an issue with the MM configuration or the permissions for the google sheet you are trying to access.

If you can post your configuration you are using I can try to help you diagnose the problem.

[XAGSolutions]

{ module: "MMM-GoogleSheets", header: "ZENDESK", position: "top_right", config: { url: "https://script.google.com/macros/s/REDACTED_FOR_PRIVACY", sheet: "COUNTLIVE", range: "A1:A4", updateInterval: 1, // minutes requestDelay: 250 // ms } },

[XAGSolutions]

[15.04.2022 14:24.59.383] [ERROR] Whoops! There was an uncaught exception... [15.04.2022 14:24.59.384] [ERROR] SyntaxError: Unexpected token < in JSON at position 1 at JSON.parse () at Request._callback (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_helper.js:47:27) at Request.self.callback (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:185:22) at Request.emit (node:events:390:28) at Request. (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:1154:10) at Request.emit (node:events:390:28) at IncomingMessage. (/Users/admin/MagicMirror/modules/MMM-GoogleSheets/node_modules/request/request.js:1076:12) at Object.onceWrapper (node:events:509:28) at IncomingMessage.emit (node:events:402:35) at endReadableNT (node:internal/streams/readable:1343:12)

[ryan-d-williams]

@XAGSolutions this is usually caused when the parameters or permissions are incorrect.

I can't directly see the problem without the link. When you type the following address into your browser, what do you see?

https://script.google.com/macros/s/REDACTED_FOR_PRIVACY?sheet=COUNTLIVE&range=A1:A4

Note that you will need to change the url to be your actual url and not the redacted one. This is the actual request the module will make for the data, so if you don't see any data as a result then it is likely a problem with your parameters or permissions.

[XAGSolutions]

@ryan-d-williams I added that REDACTED_FOR_PRIVACY for this post. I have the actual correct link in my config file.

[XAGSolutions]

This is what I see when I go to the actual link.

[XAGSolutions]

I also see this when I am logged in.

[XAGSolutions]

@ryan-d-williams any advice on correcting this? I have followed the walkthrough again on a new account and I am still getting the same line 11 of code error.

[SARCux]

I have the same problem TypeError: Cannot read property '0' of undefined (Zeile 11, Datei "Code")

[ghost]

I have the same problem TypeError: Cannot read property '0' of undefined (Zeile 11, Datei "Code")

same here...any solutions??

[ryan-d-williams]

This is what I see when I go to the actual link.

If this is what you see when you go to a link that is formatted like: https://script.google.com/macros/s/REDACTED_FOR_PRIVACY?sheet=COUNTLIVE&range=A1:A4 (where READACTED_FOR_PRIVACY) is replaced with your actual sheet id

Then it is definitely a permissions issue. It means your sheet has not been set for the "anyone" settings, which is why you're seeing that permissions screen.

I went ahead and ran through the directions again to make sure they still work, and they do. It seems as though Google has updated the buttons to be in slightly different locations. I'll work to update the directions in the future.

@XAGSolutions the way to fix your problem is to double check your permissions as they are currently not set properly. They must be set so that anyone can access the script, even those not signed in (since your MM is not signed in). Feel free to post a screenshot of where you are setting the permissions and so we can give feedback if you are selecting the correct options.

[ryan-d-williams]

@SARCux @shiring11

Based on past issues and this thread, the most common cause of this problem is permissions not being set properly. If you do not have sensitive data, please post your configuration that you are using and I can make sure it is in fact a permissions problem and not something else. If you do have sensitive data, double check your permissions to make sure they are set appropriately.

If you go to https://script.google.com/macros/s/[YOUR SHEET ID]?sheet=[YOUR SHEET NAME]&range=A1:A4 after replacing those values in brackets with your values, you should see actual data, as in the screenshot below. If you see a permissions screen that means your permissions were not set correctly.

[ghost]

@SARCux @shiring11

Based on past issues and this thread, the most common cause of this problem is permissions not being set properly. If you do not have sensitive data, please post your configuration that you are using and I can make sure it is in fact a permissions problem and not something else. If you do have sensitive data, double check your permissions to make sure they are set appropriately.

If you go to [https://script.google.com/macros/s/[YOUR](https://script.google.com/macros/s/%5BYOUR) SHEET ID]?sheet=[YOUR SHEET NAME]&range=A1:A4 after replacing those values in brackets with your values, you should see actual data, as in the screenshot below. If you see a permissions screen that means your permissions were not set correctly.

Hello,

I tried everything, I went through the instructions step by step. I know there is a permissions problem. I don't know how to change it.

my config:

module: "MMM-GoogleSheets", header: "Finnis Stundenplan", position: "bottom_right", config: { url: "https://script.google.com/macros/s/AKfycbzRHXjczblbZdLL72twrTkmbMAzQcK5JONVPPfOWEMRG89o-kDR/exec", sheet: "Finns Stundenplan", range: "A1:H12", cellStyle: "invert", stylesFromSheet: ["text-align", "color"], customStyles: ["font-size: 20px", "padding: 3px"], headerStyles: ["font-weight: bold"], styleFunc: (rowNum, colNum, cellProps) => {if(rowNum%2 == 0){return "background-color:#b4b4b42e;"}} }

[ryan-d-williams]

@shiring11 thank you for sharing that data. I might have potentially found the problem.

What is the actual sheet name for that spreadsheet? The name at the top is "Finns Studenplan" that is not the sheet name, that is the document name. What should go in the "sheet" portion of your configuration file is the sheet name (located at the bottom of your spreadsheet, not the top).

[ghost]

@shiring11 thank you for sharing that data. I might have potentially found the problem.

What is the actual sheet name for that spreadsheet? The name at the top is "Finns Studenplan" that is not the sheet name, that is the document name. What should go in the "sheet" portion of your configuration file is the sheet name (located at the bottom of your spreadsheet, not the top).

That was the solution!!

i didn't see the table name (below), renamed it now - it works!

Many thanks for your help!

[ryan-d-williams]

@XAGSolutions @redshiftgraphics @SARCux @YeetGott Recommend looking at the fix above in case it also helps you. As a summary 2 things you need to check:

1. You are actually using the sheet name, not the doc name
2. You have set the permissions to anyone, even anonymous

I'll leave this issue open for a few weeks if anyone else wants to comment before closing!

[SARCux]

Hi, @ryan-d-williams here my config

{ module: "MMM-GoogleSheets", header: "Stundenplan Schule", position: "top_center", config: { url: "https://script.google.com/macros/s/AKfycbxI3aP6W4Z8d_Dt3QYvAgj7RvZUPqQyCeX7MtNnD0cq4vFIhf1d4dJTbFhkH4LJNrx8Zg/exec", sheet: "Stundenplan", range: "A1:F7", cellStyle: "invert", stylesFromSheet: ["text-align", "color"], customStyles: ["font-size: 12px", "padding: 3px"], headerStyles: ["font-weight: bold"], styleFunc: (rowNum, colNum, cellProps) => {if(rowNum%2 == 0){return "background-color:#b4b4b42e;"}} } },

[1Cedric1]

Hello @ryan-d-williams and thank you for the opportunity to use your module.

I encounter the same situation as desribed below (TypeError: Cannot read property '0' of undefined (Zeile 11, Datei "Code"))

As I could see the permission, the option "anyone, even anonymous" is not available, when I want to deploy.

Could that be the cause of this error?

Thank you for your advice !

[ryan-d-williams]

Hi, @ryan-d-williams here my config

{ module: "MMM-GoogleSheets", header: "Stundenplan Schule", position: "top_center", config: { url: "https://script.google.com/macros/s/AKfycbxI3aP6W4Z8d_Dt3QYvAgj7RvZUPqQyCeX7MtNnD0cq4vFIhf1d4dJTbFhkH4LJNrx8Zg/exec", sheet: "Stundenplan", range: "A1:F7", cellStyle: "invert", stylesFromSheet: ["text-align", "color"], customStyles: ["font-size: 12px", "padding: 3px"], headerStyles: ["font-weight: bold"], styleFunc: (rowNum, colNum, cellProps) => {if(rowNum%2 == 0){return "background-color:#b4b4b42e;"}} } },

@SARCux your setup looks okay. What problem are you encountering?

[ryan-d-williams]

@1Cedric1 I had to type most of that into google translate so I may be incorrect, but the "everyone" option should be what you're looking for. Can you post your configuration? Make sure you are using the sheet name NOT the document name.

[1Cedric1]

Hello @ryan-d-williams, I could test it again in a browser, and get the codes, even if not connected with my google account: as in Google Sheet. So it should not be a permission problem I suppose. but on the mirror, it still gets stucked Do you have an idea?

Here the picture of the google Sheet and MMM-config

[ambernagel]

I'm having the same issue. I've followed all of the directions on the new branch. My Sheet is still called Sheet1. Here's my code:

{ module: "MMM-GoogleSheets", header: "Notes", position: "top_right", classes: "default everyone", config: { url: "https://script.google.com/macros/s/AKfycbytnaUsKNDr_8Rpec97vIz0x7Bt-Vy_CCHJKH8X6cmziVQQCpuQrPyFZR2e3MHzPmcNmA/exec", sheet: "Sheet1", range: "A1:C3", } }

[ryan-d-williams]

@1Cedric1 your configuration is still incorrect. Your sheet should be "Stundenplan" not "FE1". When I change the sheet name it works as expected. It is hanging because you have entered a sheet in the config that does not exist.

[ryan-d-williams]

@ambernagel your project was not deployed correctly. When I try to access the link, I see a permissions error message. If it is deployed to anyone (even anonymous) then this message will not appear when the link is accessed.

[ryan-d-williams]

This issue is stale so I'm closing it. Feel free to reopen if there are still issues. Note that:

1. The README has been updated for the new version of google apps script (with pictures)
2. The module now shows an error message if a sheet name is entered that does not exist