Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Automatically save a table to the cloud using google spreadsheet

edited August 2013 in Questions Posts: 277

Hi there,

Ok, this is a long shot:

Do you guys and girls think it is in any way possible to save a variable or a table to the cloud using google docs?

I found this http://productforums.google.com/forum/#!topic/docs/iCfNwOliYKY and am wondering if we could somehow use codea's http functions to send custom urls that'd populate a google spreadsheet using that rather simple method.

That would be pretty useful for many applications I believe...

Comments

  • BriarfoxBriarfox Mod
    edited August 2013 Posts: 1,542

    I don't see why not! Give it a go. I'm not sure it's the most efficient way to save a table but it is super useful.

  • BriarfoxBriarfox Mod
    Posts: 1,542

    Heck this would be a great way for my autogist to check the number of installs! Very cool!

  • Posts: 277

    Hmmm, I can't get the method describe on that link to work (in the sources, I don't find the url with the "amp;" part, for instance)... Maybe Google changed its way to submit responses?

  • BriarfoxBriarfox Mod
    Posts: 1,542

    it works the url has changed somewhat. Use ?entry.######=text&submit=Submit

  • Posts: 277

    Fantastic!!!

  • Posts: 277

    oops, have you tried it? it seems to work, but actually gives empty entries in the google spreadsheet

  • BriarfoxBriarfox Mod
    Posts: 1,542

    Mine works fine.

  • edited August 2013 Posts: 277

    Ok, I must be doing something wrong...

    I've got this in my code:

    ~~~link = "https://docs.google.com/forms/d/17EefvQmyuXg82hSPH_o2PccJ86QZqG6ZZd_XQJKmGeE/formResponse?entry."..urlToSave.."=text&submit=Submit"
    openURL(link)~~~

    Safari is called as expected when the code runs, and I get a "your answer has been recorded" message, but the cell stays empty.
    I must have been doing something wrong with my form...

    Cheers.

  • BriarfoxBriarfox Mod
    Posts: 1,542

    what is the urlToSave? This should be a static id to the field.

  • Posts: 277

    Oh sorry, it's simply a number (hi score in that case)

  • Posts: 521

    I made some small attempts using OAuth, but it was a bit messy, and seemed to assume access to a web browser. But if it is usable, here is my code for logging in an storing and loading data from a spreadsheet.

    Sheet = class()
    
    function Sheet:init()
    end
    
    function Sheet:withToken(callback)
        -- Currently assume that a device code exists
    
    end
    
    function Sheet:getDeviceCode()
        local deviceCodeUrl = "https://accounts.google.com/o/oauth2/device/code"
        local data = "client_id=" .. CLIENT_ID .. "&scope=https://spreadsheets.google.com/feeds"
    
        http.request(deviceCodeUrl, function (data)
            local jsonData = json.decode(data)
    --        print("user code is " .. jsonData.user_code)
            saveGlobalData("deviceCode", jsonData.device_code)
            saveGlobalData("userCode", jsonData.user_code)
    --        openURL("http://www.google.com/device")
            openURL("http://social-layer.herokuapp.com/deviceCode/" .. jsonData.user_code)
        end, function (error)
            print("Error " .. error)
        end, {
            method = "POST",
            data = data,
            headers = {
                ["Content-Type"] = "application/x-www-form-urlencoded"
            }
        })
    end
    
    function Sheet:getAuthToken(callback)
        local getTokenUrl = "https://accounts.google.com/o/oauth2/token"
        local deviceCode = readGlobalData("deviceCode")
        local data = "client_id=" .. CLIENT_ID ..
                     "&client_secret=" .. CLIENT_SECRET .. "&code=" ..
                     deviceCode .. "&grant_type=http://oauth.net/grant_type/device/1.0"
    
        print("getAuthToken " .. data)
        http.request(getTokenUrl, function (data)
    --        print(data)
            local jsonData = json.decode(data)
    --        print("access token is " .. jsonData.access_token)
    --        print("refresh token is " .. jsonData.refresh_token)
            saveGlobalData("accessToken", jsonData.access_token)
            saveGlobalData("refreshToken", jsonData.refresh_token)
            if callback then callback() end
        end, function (error)
            print("Error " .. error)
        end, {
            method = "POST",
            data = data,
            headers = {
                ["Content-Type"] = "application/x-www-form-urlencoded"
            }
        })
    end
    
    function Sheet:refreshAuthToken(callback)
        local getTokenUrl = "https://accounts.google.com/o/oauth2/token"
        local refreshToken = readGlobalData("refreshToken")
        local data = "client_id=" .. CLIENT_ID ..
                     "&client_secret=" .. CLIENT_SECRET .. 
                     "&refresh_token=" .. refreshToken ..
                     "&grant_type=refresh_token"
    
        print("refreshToken")
        http.request(getTokenUrl, function (data)
     --       print(data)
            local jsonData = json.decode(data)
     --       print("access token is " .. jsonData.access_token)
            saveGlobalData("accessToken", jsonData.access_token)
            if callback then callback() end
        end, function (error)
            print("Error " .. error)
        end, {
            method = "POST",
            data = data,
            headers = {
                ["Content-Type"] = "application/x-www-form-urlencoded"
            }
        })
    end
    
    function Sheet:availableSheets() 
        local accessToken = readGlobalData("accessToken")
        local url = "https://spreadsheets.google.com/feeds/spreadsheets/private/full?" ..
                    "alt=json&" ..
                    "access_token=" .. accessToken
    
        print("get sheets")
        http.request(url, function (data) 
            print(data)
        end)
    end
    
    function Sheet:getCells() -- from my spreadsheet with specified key
        local accessToken = readGlobalData("accessToken")
        local key = "0AsDhnQgjRtV2dEVPQXkwWlRTWW5CM2RkWGJPSVNnUkE"
        local url = "https://spreadsheets.google.com/feeds/cells/" ..
                    key ..
                    "/od6/private/full?min-row=1&min-col=1&max-col=2&" ..
                    "alt=json&" ..
                    "access_token=" .. accessToken
    
        print("get cells ")
        http.request(url, function (data)
            local jsonData = json.decode(data)
            local cells = {}
            for k,cell in pairs(jsonData.feed.entry) do
    --            print(cell.id["$t"])
    --            print(cell.title["$t"] .. " - " .. cell.content["$t"]) 
                table.insert(cells, List(cell.content["$t"]))
            end
    
            theList = List{
                name = "Remember",
                items = cells
            }
        end, function (error)
            print(error)
        end)
    end
    
    function Sheet:set(col, row, value)
        local key = "0AsDhnQgjRtV2dEVPQXkwWlRTWW5CM2RkWGJPSVNnUkE"
    
        local cellurl = "https://spreadsheets.google.com/feeds/cells/" .. key .. "/od6/private/full/R" .. row .. "C" .. col
        local data = [[<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">
          <id>]] .. cellurl .. [[</id>
          <link rel="edit" type="application/atom+xml"
            href="]] .. cellurl .. [["/>
          <gs:cell row="]] .. row .. [[" col="]] .. col .. [[" inputValue="]] .. value ..[["/>
        </entry>]]
    
        local accessToken = readGlobalData("accessToken")
        local url = "https://spreadsheets.google.com/feeds/cells/" ..
                    key ..
                    "/od6/private/full/R" .. row .. "C" .. col .. "?" ..
                    "access_token=" .. accessToken
    
        print("set cell data")
        http.request(url, function (data)
    --        print(data)
        end, function (error)
            print("Error " .. error)
        end, {
            method = "PUT",
            data = data,
            headers = {
                ["Content-Type"] = "application/atom+xml",
                ["If-Match"] = "*" -- what does this do? 
            }
        })
    end
    
  • Posts: 277

    This code retrieves data from a spreadsheet?

  • Posts: 277

    @Briarfox, would you mind pasting the url you use to add text to a spreadsheet? I still can't manage to make it work and I suspect it's something little I'm overseeing. You can replace your key by xxxxxxxx's of course :)

Sign In or Register to comment.