Google Calendar, Google Sheets and appsheet integrations

[johnni88]

Hi Everyone

Does anyone know how to link Google Calendar and Appsheet together, whilst at the same time having all the data recorded from Gcal and Appsheet onto Google Sheets? So all 3 platforms run in unison and I can edit everything and make updates.

 I believe I might need a special code to use with Appscript to make it work but I dont know how to go about it

Thanks

[AleksiAlkio]

You can connect the gCal as a data source with your app. Then the app is connected with the gCal. The more challenge is the gSheet that it works 100% in both ways. Some of it you could probably done with IFTTT.com recipe but does it work 100% in both ways, I'm not sure. GAS is of course one option as you mentioned. In generally you could use AppSheet API, but the problem is that it doesn't recognize when you make updates or adds directly in the gCal.

[johnni88]

could a solution like this help me? - https://www.youtube.com/watch?v=MOggwSls7xQ

I havent got a coding background and i have been just trying different things to see what I come up with and trying to use A.I. to help me. I just need a bridge between the platforms and i suspect appscript is going to need to be involced or something similar.

Ideally, all i need it the name/title, start date, start time and location. If i can get appsheet talking to Gcal & appsheet talking to Gsheets - I can use the Appsheet servers (not google servers) to edit the changes going to Gsheets and Gcal. I just need help working on an expression or some technical trick I can use in the backend of Appsheet. There is not a Gsheet add on for issues like this? surely Google is on to this bridging issue?

If i can get this working it'll make all the difference moving forward with my objective.

Thanks for your reply Aleksi. You've pulled me out of some trouble many times! I hope this one does not stump you 🙂 😉

[AleksiAlkio]

"all i need it the name/title, start date, start time and location"

Do you need it only for one direction.. like only when it's created in gSheet?

[johnni88]

The backstory is - I run a team of electricians that visit homes and appsheet is the core management platform for me to reach them in the field.

there are 2 apps in question: 1 - the homeowner books a convenient time via the appsheet app I send directly to them, and that specific appsheet app is connected to Gcal.

the second (2) app is the field app for my workers. The calendar they see is the same Gcal as above, so they can structure their time according to when the client's preferred time is. This helps coordination between everyone with less human interaction by using 1 Gcal. The field workers can not edit the client times, only the client can and myself. But if i need to edit the times I want to be able to do it from Gsheets and/or Gcal.

It's only simple data recorded onto Gsheet being name, start date, start time and location. Also, everything is laid out in one spreadsheet and is easier for project management. Plus Gsheet has multiple functions I can add on which leverages my capabilities with other things I do. So, that there lays my problem. I need them all integrated somehow. 

Furthermore, I can see this wont be the only time I will need this 3-way integration between all platforms in the future. All these 3 platforms are way too important not to be able to speak to one another comprehensively.

[johnni88]

Also, instead of going through the google servers. Can you not use appsheet servers as a point of reference instead?? So Appsheet is the master controller? That why I ask if Appsheet specifically has a expression I can use somewhere so appsheet writes to Gcal>and then appsheet writes to Gsheets simultaniously. 

Then I simply connect the second (2nd) app from the field workers to reference the same calendar that the client is writing to from appsheet. Making the appsheet servers the master controller.

[AleksiAlkio]

So.. in your case, would it be enough to write only the event from the gCal to GSheet?

[John_Pearson]

Thats the short way around it but I lose the functions I have interconnected in Gsheets for things that branch off. I want to try getting this under control for business growth and not having delegated planners switch between systems. I just want them to be concerned with 1 focal point and share a Gsheet with them. hence the integration question between all 3.

[AleksiAlkio]

Let me play with it, I have an idea..

[AleksiAlkio]

Actually, you can control this with AppSheet. The main point here is that because you are able to identify what is the "Row ID" for the gCal event, you are also able to update the gCal event with an action.. and the opposite.

So.. when the client creates the gCal event, add an action that writes the data to gSheet. Make sure the Row ID is copied as well. The best option would be to use it as a Key value. Then you would have 1 to 1 relationship between the gCal and gSheet. Trigger the action with the Event action (in the form view).

Now if someone modifies the row in gSheet with the app, you can do the opposite with an "Execute on a set of rows" action. When the gSheet row is modified, it updates the event in your gCal as well. I tested this quickly and it works. The app is able to update the Title, Start, End and Location. Probably other values as well, I just didn't test others.

[AleksiAlkio]

The only thing that doesn't work.. if someone modifies the event directly in gCal, the app is not able to identify this as it's not happening in the app itself. Meaning.. the gSheet is not then updated. If that's needed as well, you could read it with a scheduled Bot like once in an hour.. or use the GAS script.

[johnni88]

Yes that is what I was saying by trying to use the appsheet sever instead of the google server. Ultimately, there was going to be at least one mechanism that wasn't going to fit and that being the Gcal. I will share a "read-only" Gcal to other giving me control over what I need control over. 

Ok, give me some time and I'll try to incorporate your solution above when I have a bit more time. I'll definitely give it a go. 

Thank you so much for your help! Like always, greatly appreciated!

[AleksiAlkio]

You're welcome

[johnni88]

Hi Alexsi

Could I trouble you for a screen recording/screenshots of how you set the actions up regarding the above, please? I need some visual help to get this working.

[AleksiAlkio]

Send your email with PM so I can share the app.

[FeasiblePeak]

So wait was this solved?