Problem with google sheet actions triggered by IFTTT

[ChrisM]

Hi Mike, I been using google sheet actions for years,  Haven't changed anything but these stopped working a few days ago.  Possibly around the same time that the latest beta installed but I'm not certain.  I use the button press in IFTTT to add a row to a google sheet and I can see the row added to the sheet but nothing happens in AM.  Appreciate any thoughts.  Thanks

Chris

[trogli...@gmail.com]

Crap, google has changed something, it’s broken here too.  Looks like the firebase messaging between the server and client is broken, I’m guessing they changed something starting Sept 1.  I’ll see if I can sort it.

 

Mike

--
You received this message because you are subscribed to the Google Groups "MppDevices" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mppdevices+...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/8381edfd-8371-4b6d-9eab-5d3335a1d785n%40googlegroups.com.

[Chris McGaffey]

Thanks, Mike.   Much appreciated.

Chris

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/061901db005d%2409b07780%241d116680%24%40gmail.com.

[Mike P 4 MPP]

Ok, I think I may have it.  The problem is they deprecated and removed the firebase (messaging) API that I was using and when doing so change the permissions as well.

I've updated the instructions, but basically you need to find your spreadsheet that you use for IFTTT and edit the script that you created there.

You need to change the AutomationOnDrive library to version 78 (click it in the code view).  If you see "head" that's my dev version, it may break occasionally.

You need to give permission to your script (and the AM library) to access your drive files:

• Touch "Project Settings".  Enable "Show "appsscript.json" manifest file in editor"

• Go to the editor to select "appscript.json"

• Replace the first line "{" with:

{
  "oauthScopes": [     "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/firebase.messaging",
    "https://www.googleapis.com/auth/script.scriptapp"
  ],

• Save the script file.

If you get a chance to try it please let me know how it goes.

[DougC]

Hey Mike. I put in the latest AM release on my carry phone. I run AM server and a few widgets on it. However following the update the AM server does not work. The widgets did play up for a bit but actually seem fine now so I'm not worrying about that for now

If I try to open it, the AM server just flashes up on screen then collapses again with the Android message 'AutomationManager keeps stopping'. Tried rebooting the phone and clearing cache just in case but no help.

I on Android 14 on a moto g53. Any suggestions what to try?

[trogli...@gmail.com]

Google forced an API upgrade to 14 at end of august so the firebase update left me stuck when I fixed that – the OS keeps killing the main process due to a background restriction on android 14 devices.  I’ve submitted an update but they need to approve it and it’s got a new permission (run as a network service) they’ll probably drag their feet or maybe baulk at.

 

The only choice for anyone with AM on 14 for now is to opt out of the beta and to try to go back to the production version that was built for android 12.  Google tells me “we will soon take action” because of the version on 12, not sure when that will be…

 

I don’t think any server will run properly on android 14 anymore with the new background task restrictions.  Or if it does it’ll probably need the main app to be opened after any restart.  But do you mean the AM app rather than the server? You usually wouldn’t use the AM Server on a carry phone (though both crash on 14).

 

 

[DougC]

Ah OK. Good old Google. I'll try opting out of beta and reinstalling then. 

I have server on my carry phone cos I sort of use it for testing though tbh it's been so long since I set myself up that way that I can't really remember my rationale.

thanks again for the swift response Mike

[trogli...@gmail.com]

The good news is fixing this IFTTT problem gave me an idea for folks to build their own AM Remote web page to replace the one that google broke…  Instructions here: https://sites.google.com/site/mppsuite/wemoondrive/am-remote-online

 

 

From: mppde...@googlegroups.com <mppde...@googlegroups.com> On Behalf Of DougC

Sent: Sunday, September 8, 2024 1:51 PM
To: MppDevices <mppde...@googlegroups.com>

--

You received this message because you are subscribed to the Google Groups "MppDevices" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mppdevices+...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/55a8904d-32ff-4f85-b1c4-84349c504645n%40googlegroups.com.

[ChrisM]

Hi Mike, where can I find the updated instructions?

Chris

[ChrisM]

Hi Mike, I missed the last post.  I followed the instructions but this is what I'm seeing:

AM Remote

Unfortunately google has changed the permissions required for this page to work to be prohibitally restrictive. Changing devices and sending events will no longer work until google extends their libraries to allow these functions.

Reload Locations
Click to show/refresh a location:

Could not fetch locations: ScriptError: Exception: Service Google Drive API has not been enabled for your Apps Script-managed Cloud Platform project. If you have recently turned on this advanced Google service, wait a few minutes for the action to propagate to our systems and then retry.

Thanks

Chris

[trogli...@gmail.com]

This is for the IFTTT integration, right?  The original instructions had you enable the google Drive API and the Google Sheets API in the “Advanced Google Services”.  If you’ve started a new script it’s a bit easier that it was in the original – in the script, click the “+” sign under “Services”, pick “Drive” and “Sheets” from the list.  If this was your old script it’s a bit odd that it’d be gone, but the step is the same – add both.

 

I’ve made a few changes to the base script, it’s probably not necessary to change but generally pick the highest version available, right now it’s 81.

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/df3d1d00-bd3c-4e3e-88ba-284a005f7edcn%40googlegroups.com.

[ChrisM]

Hi Mike, I created a new script.

I use the interactive page for controlling my devices remotely from my browser (https://sites.google.com/site/mppsuite/wemoondrive/am-remote-online?authuser=0Due)

I also rely on IFTTT to remotely control devices.  I have a number of IFTTT button press applets that add a row to a google sheet.  I can see IFTTT add the row to the sheet but it doesn't seem to be picked up by AM; i.e. nothing happens.  The row remains in the sheet and no action is taken by AM.  In the past, the action would be picked up and executed by AM and the row would be deleted.

I missed the steps to add the services; Drive and Sheets.  I selected version 78 of the AutomationOnDrive library as I saw that in your post earlier.  I just changed that and added the two services to the script and it now loads the location, and lists all of the devices and their status. But it doesn't refresh and pressing any of the device to toggle it on or off results in the switch being replaced with "On..." or "Off..." but then nothing happens after that.

Thanks

Chris

[trogli...@gmail.com]

Let’s focus on the IFTTT script first since you will have added your own permissions and you can check the logs.  I can’t easily test permissions on the web app because I already have all of the authority I need so it always works (which is pretty frustrating).

 

Confirm that your AoD app is at the beta level from play, or better yet at the sideloaded beta level from here (until google approves it): https://sites.google.com/site/mppsuite/downloads/wemomanager-betas.  This is needed for the new Firebase API.

 

In your script you you can add a function like this one with your own device name or UDN.

function testToggle() {

  SpreadsheetApp.getActive().appendRow(["Office Switch","toggle"])

  onChangeTrigger({source: SpreadsheetApp.getActive()});

}

 

Clear out anything left in the spreadsheet, then select the testTogggle function from the top dropdown in the script and hit “run”.  If you need to grant permissions (you should need to if you haven’t yet) they’ll pop up before running the script.  If it doesn’t work check the result under “Executions”.  If it does, have a look at the AoD log view to see if the command arrives. It should also be gone from the spreadsheet.

 

Once it works it’d be helpful if you could try the webapp again.  First confirm whether whatever you did worked physically, then refresh the web app using the location button to see the result (it doesn’t refresh automatically anymore, that was causing too many problems).

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/fb8e049e-8cca-4074-a0f7-f1b9e39c0aadn%40googlegroups.com.

[Mike P 4 MPP]

Crap, it's not going to work yet, I'll still need to add something to get permission for the script to send messages to the AM server :(.  I'll let you know if I can sort something out.

[Mike P 4 MPP]

Ok, got it going.  You'll need to edit your trigger script to change the AutomationOnDrive library version to "82".

[ChrisM]

Hi Mike, I edited the script and the IFTTT integration is still not working.  I can see the rows added to my spreadsheet but no action is taken by AM and the row is not deleted by the syste.

The good news is that the web page is now working.  I can turn devices on or off, and AM completes the action. I just need to refresh manually as you said.

Thanks

Chris

[trogli...@gmail.com]

Cool, almost there then.  I had to delete a deployment a short time ago so you might have been caught up by that.  Go back into the IFTTT trigger script, select the AM library and make sure it’s at the latest version.  If you can, use that testToggle script (edit it to a switch name) and run it.  Or if there’s already something stuck in the spreadsheet, the “test” script should find it as is.

Then pop into the execution log for the script and let me know what error you get.

--
You received this message because you are subscribed to a topic in the Google Groups "MppDevices" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mppdevices/-yDgPTFcBFo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mppdevices+...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/c67467f6-9a22-4ea2-83fd-ca6edc0bf22dn%40googlegroups.com.

[ChrisM]

Hi Mike I upgraded AM to the latest on Play 17.5.3-584.  No change in behaviour.

So then I added this function to the script.

function testToggle() {

SpreadsheetApp.getActive().appendRow(["MH_LGHT_EastSoffit", "ON"])

onChangeTrigger({source: SpreadsheetApp.getActive()});

}

I'm not sure what I got wrong but when I run the testToggle function i get an error:

TypeError: Cannot read properties of null (reading 'appendRow')

testToggle

@ Code.gs:26

Thanks

[trogli...@gmail.com]

Try the debugger – it seems like “getActive()” doesn’t know which spreadsheet you mean, and it should because you’re running the trigger from there…  You could change that code to getActive() into a var first and check the name with a debugger to see (though I suspect it’s returning nothing).

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/4fd40fa9-197c-44a2-b783-d773b069de35n%40googlegroups.com.

[ChrisM]

Hi Mike, yes using the debugger I see it halts at "getActive".  When you say "it should (know what spreadsheet) because you're running it from there" it got me wondering if that was the case for me.  So from the sheet, I selected extensions, Apps Script and recreated the script.  Now when I run testToggle it is adding rows top the spreadsheet but getting the error:

ReferenceError: onChangeTrigger is not defined

testToggle

@ Code.gs:28

Thanks

[trogli...@gmail.com]

Right, the script has to be added to the sheet used from IFTTT as that’s the context for it to run.  Sounds like you may have missed one of the setup steps – maybe adding the right AM library or running “setup” on the new script to install the trigger?  If you did both of those you may need to publish the script – test mode should be enough – I’m not certain whether that’s necessary.

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/29821dfc-de81-401c-a0e8-8337a428e98bn%40googlegroups.com.

[ChrisM]

I don't recall seeing a step involving "setup".  Do you mean deploy?

[trogli...@gmail.com]

Double checked in case I missed it – it’s just after adding the permissions:

Open the code.gs file select the "setup" function from the dropdown, and then the run (triangular) button.

This function installs the change trigger to the sheet.

To view this discussion on the web, visit https://groups.google.com/d/msgid/mppdevices/8d878d97-b707-4fbc-bc5a-d2b8f4d0ef86n%40googlegroups.com.

[ChrisM]

Thanks Mike all good now.  I was following the wrong instructions. Sorry for wasting your time.