Current Server Options - August 2019

adent42

@tatiang For the GET you will need to add ?tableId=theactualtableid

You can do a get on it's down, but the table has to exist in the same format.

My suggestion, if you're mostly a "pull" app, is to do a POST once during development to get a table with the right ID and format. Then fill in the data on the sheet and you can use PULL from that point onward.

tatiang

@adent42 Thank you, the tableId expression did the trick.

adent42

@tatiang By way of explanation, the script is set up to allow for multiple tables / sheets. So you have to tell it what sheet you want. You could, in theory, change the script to pull from only one table and hard code the sheet ID OR create a HashMap that maps more friendly names to table Ids, or any a number of things. The script is meant as a starting point. Hope it helps!

tatiang

Thanks, @adent42. I will be using multiple tables so I've added the table ID for my test table.

Armelline

@adent42 said:
@tatiang By way of explanation, the script is set up to allow for multiple tables / sheets. So you have to tell it what sheet you want. You could, in theory, change the script to pull from only one table and hard code the sheet ID OR create a HashMap that maps more friendly names to table Ids, or any a number of things. The script is meant as a starting point. Hope it helps!

Hope to get a chance to have a play with this tonight! I'll definitely need to be using a single game to send and pull lots of different tables (one per student) so happy to see it sounds like that will be possible!

adent42

@Armelline the "setup" step helps you match a Google Sheets document to the script. If you keep track of different document IDs, you could write to different document per class or student. That might be a bit more scalable than a single doc with a sheet per student (depends on how many students you have).

vafurlogi

This is really good! Non english characters in table will throw an error thoug, any idea why that is? I tried characters "Á - Þ - Æ - Ö - Ð" and they all cause the callback -1.

tatiang

@adent42 If you have any idea how I might get/push an entire row, entire column or individual cell value in a Google Sheet from GameSalad, that would be really helpful. Not sure if that's possible!

adent42

The "easy" way is to use a tmp table. Basically you have a table whose sole purpose is for sending and receiving data. Once the data is transacted, you copy it back to your "master" table.

tatiang

@Armelline, @adent42 and @solnika Any further progress on this?

I'd love to see a demo of pushing and pulling data with a Google Sheet.

I've been using a competitor's product that is free and has spreadsheet integration with AirTable. It's awesome how easy it is to push/pull the data but I greatly prefer GameSalad for development. This is the missing link for the project I've been working on for the past 1+ year.

adent42

The main problem is how weird our format is. We either need a plugin system that lets you transform table data before a request or you can do that yourself via something like amazon lambda or google cloud functions. We can do it on google sheets because I can program it to transform our format. Last time I checked, you can't do that on airtable.

adent42

So it still has limits, I just set up some "Actions" on https://pipedream.com/

GameSalad - Post Table Data Success
GameSalad - Parse Table Data
GameSalad - Create Table Response

To add them to your workflow use: 'Non-app action' and search for GameSalad.

I haven't fully tested them yet, but you can use these to post and get data from AirTable or any other service they support.

Cheers!

solnika

Thanks I will try it today, hope I will figure out how to set it up.

adent42

Again, this system has limits, though they're not explicit about them. The good thing is if you can figure out how to make this work, it's just node.js compatible javascript code. So you can use this for development and testing and then once you get to the point that it's too much, you can adapt the code to another service like google cloud functions, amazon lambda, azure functions, or just running your own node.js server.

sinbot

Is it possible to use a Raspberry Pi 4 as a server for an app?

SSDKOF

thanks for the posts. Do you know of a simpler way to get data out of an app?

solnika

Merry Christmas everybody!

For those who interested I found how we can get/post with more control.

I just want to say that basically I have no clue what I'm doing, I play around and test things until I get what I need so I'm not really sure if it's the right way to do it.

I used the request url to send info about the data I want (goes after the "exec" and the "tableid");

exec?tableId=id354647&rw=1&cw=1&rn=6&cn=3

"rw" is the row number to start from, 1 is the 3th row of the sheet table (first two store the column type and header info).

"cw" is the column number to start from, 1 is the 2nd column in a given row (first is the row info).

"rn" is the number of row to get, the minimum must start at 3 (it include the first two row and then it adds the wanted rows).

"cn" is the number of column to get, minimum must start at 2 (it include the first column and then it adds the wanted columns).

Each value after those should be replace with an attribute;

"https.......rw="..attribute1.."&cw="..attribute2.."&rn="..attribute3.."&cn="..attribute4

The script needs to be added with;

var rw = e.parameter.rw;
var cw = e.parameter.cw;
var rn = e.parameter.rn;
var cn = e.parameter.cn;

I put it after;

function getTable (e){ var tableId = e.parameter.tableId; // Get the table ID from the request.

and the line:

// This gets the full range of non blank cells
var range = sheet.getDataRange();

need to be replaced with;

var range = sheet.getRange(rw, cw, rn, cn);

I also used it to post data to a specific cell in the table;

exec?tableId=id3546478rp=8&cp=1&md=hello

In the script I put;

var rp = e.parameter.rp;
var cp = e.parameter.cp;
var md = e.parameter.md;

Again I put it after;

function getTable (e){ var tableId = e.parameter.tableId; // Get the table ID from the request.

And also I put;

var cell = sheet.getRange(rp, cp);
cell.setValue(md);

after;

// Open the doc.
var doc = SpreadsheetApp.openById(DOC_ID);
var sheet = doc.getSheetByName(tableId);
var sheett = doc.getSheetByName(222);

That's that I'm sorry for my English, still, I hope it will be useful.

Merry Christmas
Enjoy

adent42

@sinbot yes, though we have no multicast /upnp detection, so you'll have to have the user enter their own IP (or hardcode it if you're just making an app for yourself).

@SSDKOF At this time there's no simpler way. The easiest so far is to use pipedream.com and add the GS related modules which will help you parse gs data. After that, you'll have to use some javascript to figure out the rest.

solnika

@adent42 any ideas about the crashes when google sheet limit reached ?
Can you help with this?

Posted about it here;
https://forums.gamesalad.com/discussion/comment/612366#Comment_612366

Thanks

Twayne2

Can somebody help me with this Google Sheet stuff? I don't know what to all change in the sheet script editor using that gisthub code. Every tableid? Only the blue-highlighted ones? Do I need the DOC_ID? Any help is very appreciated.

adent42

DOC_ID is grabbing from the doc the script is attached to, so you shouldn't need to change that.

tableId you send with your request in the Get or Send table behaviors. So it would be:

https://<google's insanely long url>?tableId=the table id of your table.

To get the table ID, you'll need to look into your project's directory. Sorry there's not a better way ATM.

The script is meant as a proof of concept and lets you sync data from a particular table into a google sheet and back out again. This will add a new sheet inside your doc if it's not there.

You could instead hardcode the tableID or ignore it (you'll still need to know it if you want your game to get a table, but you don't have to do what we do and name your sheet the same as the tableID).

You'll need to brush up on js programming and make the modifications yourself (though we can help here if you give us enough detail. I'd start a new thread though).

Twayne2

Thanks for the prompt reply @adent42. So, for the url in the network behaviors, do we use the script's url, or the sheet's? From what you said, it sounds like the sheet's?
And once I publish the script, it looks like it would update the Sheet I created before going to tools>script, not make a new one...?

Don't worry, I stink at actually coding. My knowledge is very, very limited. That's why I was coming here for help haha. But I understand helping a newbie like me would be very time-consuming, so no worries. I appreiate the help you've already done. Will try some more and see if I have any luck.

One last question if I may. Do I keep the "id" in "id[digits]", or subtract it and leave just the numbers as the id of the table?

adent42

The scripts URL, not the sheet's. It's whatever URL the web app deploys to when you publish your sheet as a web app.

adent42

Keep the id

adent42

If it's not secret, just explain what you want to do and I can try to explain how to modify the code here (sharing the knowledge).

If it is sensitive, then PM me and I'll reply when I can.

I just need to buckle down and make a video at some point.

chriswatson

Thank you so much for this amazing thread. I have got so many amazing tricks of google docs and of other google developers software. It is so amazing. Well I have tried some of this trick before in by project which is on google docs but I always use [color=black]google doc in landscape mode[/color]. I have read some helpful suggestions that will help me in my project. All the knowledge that I have read in the thread, it is so informative and I will definitely use in my projects. So thank you so much for your help. ☺️

chriswatson

Thank you so much for this amazing thread. This thread is so amazing. I have got so many solutions in this thread. I am facing some problems for the last few weeks. I am searching for these problems and I found this thread. When I found this thread then without wasting time I just used to click here on this thread and one by one I have read the comments. In this thread, I have got the solution of my all problems. So Thank you so much today you have solved all my problems. ☺️

NKBDL

I just read this thread and I feel we can use a piece of this in the near future! Thanks guys.

Armelline

@adent42 With Appformative down again, it's time to transition Bryan's educational apps to another server, and Google Sheets seems like a good solution as Bryan is comfortable in Google Sheets and it's a good place to centralise the play data so he can do whatever he wants with it. He has about 300 students that each need their own sheets for multiple games. Getting a game sending and receiving the table to a sheet using your demo script was a piece of cake (thanks!), but now I need to figure out how to pump the same in-game table to a different sheet depending on the user logged in. Using Appformative we would put a unique 7 digit user number (their school ID number) in the first cell of the table, and Appformative would then direct the sent table to the appropriate place. I'm hoping to achieve something similar with Google Sheets in order to minimise the changes needed. Can you give me some advice on how to send the table to a different sheet depending on the student? I'm sure Bryan would also be happy to pay for some direct assistance with this. I know GameSalad inside out after all these years, but it's been several decades since I even knew cursory JS. Thanks!

adent42

Two ways, you should read that cell of the table data before selecting what spreadsheet to use OR you could make that part of the expression for the URL and send it as part of the URL params and use the URL param to choose the table.