Lua - How do I extract a row of data from a JSON table (Google Sheets API)?-CodePudding

I’ve started to explore the Google Sheets API with Lua, and while I can retrieve a range of values, i can only work out how to return a column worth of values, but I’d also like to return a row too.

Here is an example of the Lua code I used to retrieve the values from cell A1 to D5 in a Google sheet. (4 columns 4 rows)

To help me track what’s coming from where, I added the name of each cell into the cell itself.

local socket = require 'socket'
local ssl = require 'ssl'
local https = require 'ssl.https'

local body, code, headers, status = https.request("https://sheets.googleapis.com/v4/spreadsheets/[mysheetID]/values/A1:D5?key=[MyAPIkey]")
print(body)
print("---------------------")

--[[ Which returns the following…
{
   "range":"Sheet1!A1:D5",
   "majorDimension":"ROWS",
   "values":[
      [
         "Cell A1",
         "Cell B1",
         "Cell C1",
         "Cell D1"
      ],
      [
         "Cell A2",
         "Cell B2",
         "Cell C2",
         "Cell D2"
      ],
      [
         "Cell A3",
         "Cell B3",
         "Cell C3",
         "Cell D3"
      ],
      [
         "Cell A4",
         "Cell B4",
         "Cell C4",
         "Cell D4"
      ]
   ]
}
]]--

To show a column, i do the following..


local json = require "dkjson"
local j = json.decode(body)
    for k,v in pairs(j.values[1]) do
    print(k,v)
end

Which returns the 1st column (below), and j.values[2] will give me the second, and so on..

1     Cell A1     
2     Cell B1     
3     Cell C1     
4     Cell D1

What I can’t work out is how I can return a row, i.e. A1, B1, C1, D1

Please could someone explain how I can retrieve any row from the list, and to be able to do that consistently, irrespective of the cell range I decide to retrieve ?

CodePudding user response：

While I'm unsure about what exactly you are looking for (you mention you are getting A1, B1, C1, D1, which corresponds to a row), if you want to retrieve transposed results, sorted by column instead of row, you can add the query parameter majorDimension=COLUMNS (if this parameter is not set, it defaults to ROWS, as you can see in ValueRange).

Therefore, you'd have to change the requested URL from:

https://sheets.googleapis.com/v4/spreadsheets/[mysheetID]/values/A1:D5?key=[MyAPIkey]

To:

https://sheets.googleapis.com/v4/spreadsheets/[mysheetID]/values/A1:D5?majorDimension=COLUMNS&key=[MyAPIkey]

Reference:

Method: spreadsheets.values.get