[HTML5] Leaderboard in Google Spreadsheet

gurigraphics

  • Posts: 688

Use Google Spreadsheet as a Leaderboard

Tutorial

Part I - Google Install

1. Go to Google Drive: https://www.google.com/drive/
2. Create a Spreadsheet
3. Insert a name on the Spreadsheet and save
4. Click in "Tools", after "Script Editor"
5. Copy and past this script in editor
Code: [Select]
// What to do when we recieve an HTTP GET request
function doGet(e) {

    if ((e.parameter['id'] == null) || (e.parameter['score'] == null)) {
        // For this simple app we only need to do one thing.   
        return ContentService.createTextOutput(JSON.stringify({
            "result": "null"
        })).setMimeType(ContentService.MimeType.JSON);

    }else  if ((e.parameter['id'] == 'undefined') || (e.parameter['score'] == 'undefined')) {
        // For this simple app we only need to do one thing.   
        return ContentService.createTextOutput(JSON.stringify({
            "result": "undefined"
        })).setMimeType(ContentService.MimeType.JSON);

    }else {
        return addUser(e.parameter['id'], e.parameter['score']);
    }
}

function addUser(id, score) {
    var score = score;
    var id = id;

    //Get the first sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var ok;
    var order;
    var total;

    // we want a public lock, one that locks for all invocations
    var lock = LockService.getPublicLock();
    lock.waitLock(30000); // wait 30 seconds before conceding defeat.
    //wrap everything in a try/catch to handle errors
    try {

        var findName = id;
        var datas = sheet.getDataRange().getValues();

        for (var i = 0; i < datas.length; i++) {
            if (datas[i][0] == findName) {
                ok = 'true';
                total = datas[i][1];
                order = i + 1;

                if (score > total) {
                    var update = sheet.getRange(order, 2).setValue(score);
                }

            }
        }

        if (ok !== 'true') {
            sheet.appendRow([id, score]);
        }

        // sorte data
        var range = sheet.getRange("A:B");
        range.sort({
            column: 2,
            ascending: false
        });

        //get new data
        var data = [];

        for (var i = 0; i < 10; i++) {
            var x = sheet.getRange(i + 1, 1).getValue();
            var y = sheet.getRange(i + 1, 2).getValue();
            data[i] = [x, y];
        }

        //return a JSON with the top ten and our user
        return ContentService.createTextOutput(JSON.stringify({
            "result": "success",
            "sheet": data
        })).setMimeType(ContentService.MimeType.JSON);


    } catch (e) { //something went wrong. Return an error
        return ContentService.createTextOutput(JSON.stringify({
            "result": "error",
            "error": e
        })).setMimeType(ContentService.MimeType.JSON);
    } finally { //release lock
        lock.releaseLock();
    }
}

What does this script do?

Quote
1. This check queue of requests for sending of data.
2. Checks if the user name already exists and updates the score if it is higher.
3. Sort the users with the highest score for the first positions and update the leaderboard.
4. Return a JSON object.


6. Click in Archive, after"Save"
7. Click in Publish, after"Deploy as web app"
8. Config this:  Execute the app as: me, your email, Who has access to the app: anyone any anonymous
9. Click in Update
10. Copy the link address of web app


Part II - Stencyl Install

1. Download the extension: https://gurigraphics.itch.io/leaderboard-google-spreadsheet
2. How to install a extension: http://community.stencyl.com/index.php/topic,30432.0.html
3. Create a project in Stencyl and  Enable the extension.


Part III - How use

First

Insert your Web app URL with this block



How to send data?

Use this block to send a name and score:



How to get data?

The data of TOP 10 players is save in Dynamic Game Attributes


To get the name of the first placed use "name1"
To get the score of the first placed use "score1"

To get the name of the tenth placed use "name10"
To get the score of the tenth placed use "score10"

Only this.


Observations

  • This is not realtime.
  • Data is updated only after each submission.
  • Sending and receiving data occur at the same time.
  • Because there is no user authentication, different users can use the same name.

Version History

Version 1.0
30/03/2017 - Ranking TOP 10 Players .



« Last Edit: March 30, 2017, 02:51:16 pm by gurigraphics »



Donni11

  • *
  • Posts: 2182
WOW! Very nice. Will be looking into this. Thank you for this wonderful extension!
Peace

LIBERADO

  • *
  • Posts: 2602
Impressive! Thank you so much for this useful extension.
I'm spanish, excuse me for my bad English.
I'm not a private teacher. Please, post your questions in the public forum.

cabinfever

  • Posts: 157
This looks to be VERY useful. Thank you!

csherrett

  • *
  • Posts: 6
This works great when outputting the game to HTML5.  However, when outputting to FLASH, I get the following error:

C:\Users\CSHERRETT\AppData\Roaming\Stencyl\stencylworks\engine-extensions/google-spreadsheet/Google.hx:8: characters 21-27 : Unbound variable __js__

Any ideas?

Craig

mervous

  • Posts: 8
This extension looks to be exactly what I need, but I am lost on how to integrate it. I have set up the google drive part ( I think) but I am not sure about the stencyl side. I am obviously a complete noob, I have managed to get a flappy bird game going and I am using it in html 5 which is what i want. Just a fun web based game for xmas. I have re skinned and I have a score and high score happening but i would love to also have a leader board either in the game end screen or under the game in html. Can anyone help me get the script to work in the game. I put in the address when created but when it comes to name and score etc are they attributes? Where does someone enter a name etc I am lost.