Creating a JSON object from Google Sheets

Question

I am trying to learn AppScript and I am using Google Sheets as an example. I want to create a simple JSON object using some data populated in the Sheet.

Table Example

Name    ID   Price   Qty
ABC     123  100      1
DEF     342   56      2
HIJ     233   90      3
IJK     213   68      5

I want the JSON out to be something like

[
  {
    "Name": "ABC",
    "ID": "123",
    "Price": 100,
    "Qty": 1
  },
  {
    "Name": "DEF",
    "ID": "342",
    "Price": 56,
    "Qty": 2
  },
  {
    "Name": "HIJ",
    "ID": "233",
    "Price": 90,
    "Qty": 3
  },
  {
    "Name": "IJK",
    "ID": "213",
    "Price": 68,
    "Qty": 5
  }
]

I started by following this Youtube tutorial : https://www.youtube.com/watch?v=TQzPIVJf6-w. However that video talks about creating each column header as a the object. Where as i want the Column name to be the key and the Row value to be the value.

Here is my current AppScript Code

function doGet() {
  var result={};
  var rewards = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  .getDataRange()
  .getValues();

  result.rewardObj = makeObject(rewards);
  //Logger.log(result.rewardObj);
  return ContentService.createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON)


}

function makeObject(multiArray)
{
  var obj = {}; 
  var colNames = multiArray.shift();
  var rowNames = multiArray.slice(0,1);
  var rowCount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getLastRow();
  var colCount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getLastColumn();

  for(var j=0;j<4;j++)
  {
    for(var i=0;i<4;i++)
      {
        //obj[colNames] = rowNames.map(function(item){return item[i];});
        obj[colNames[j][i]] = multiArray[j][i];
      }
  }
  Logger.log(rowCount)
  Logger.log(colCount)
  Logger.log(multiArray[57][12]);
  return obj;
}

The output is a single object

{"rewardObj":{"Name":"ABC","ID":"123","Price":"100","Qty":"1"}}

PS: I am not a programmer, I am just learning some scripting in a hackey way. Apologies for not knowing the basics :)

Answer 1

I know it's been a while, but I came up with a slightly more flexible solution. I've created an aptly named function that converts spreadsheet data to objects, given a set of headers and a set of rows.

function convertToObjects(headers, rows)
{
  return rows.reduce((ctx, row) => {
    ctx.objects.push(ctx.headers.reduce((item, header, index) => {
      item[header] = row[index];
      return item;
    }, {}));
    return ctx;
  }, { objects: [], headers}).objects;
}

You can call the above function like this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
var [headers, ...rows] = sheet.getDataRange().getValues();
var objects = convertToObjects(headers, rows);

If you need to ignore the first row and get headers from the second row (like I do), then you can do this instead:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
var [_, headers, ...rows] = sheet.getDataRange().getValues();
var objects = convertToObjects(headers, rows);

If you have any improvements to this, please let me know in the comments below.

Answer 2

edit 2022 : for headers of columns and headers of rows as follows

yourJsonName	col1	col2	col3	col4
row1	1	2	3
row2	4		6	4
row3	7	8	9	8

try

function myJson() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var jsonString = `var ${sheet.getRange(1,1).getValue()} = ${JSON.stringify(data2json(headers, rows))}`
  Logger.log (jsonString)
}
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}

var [headers, ...rows] = [['yourJsonName', 'col1', 'col2', 'col3', 'col4'], ['row1', 1.0, 2.0, 3.0, ], ['row2', 4.0, , 6.0, 4.0], ['row3', 7.0, 8.0, 9.0, 8.0]]
console.log (`var ${headers[0]} = ${JSON.stringify(data2json(headers, rows))}`)
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}

with saving in drive ... adapt names as necessary (YourSheetName, YourFolderName, YourFileName)

function myJson() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var jsonString = `var ${sheet.getRange(1,1).getValue()} = ${JSON.stringify(data2json(headers, rows))}`
  var folders = DriveApp.getFoldersByName('YourFolderName');
  if (folders.hasNext()) {
    var folder = folders.next();
    saveData(folder, 'YourFileName.json',jsonString);
  }
}
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}
function saveData(folder, fileName, content) {
  var children = folder.getFilesByName(fileName);
  var file = null;
  if (children.hasNext()) {
    file = children.next();
    file.setContent(content);
  } else {
    file = folder.createFile(fileName, content);
  }
  Logger.log(file.getUrl())
}

Answer 3

I, too, ran into this issue. The answer lies within your Startup.cs file.

First, the reason for which the "Invalid 'Content-Type' header" appears even while using GraphQL.Upload is because (at the time of this answer) the GraphQL.net middleware only checks for the three different Content-Type headers and errors out if none of those match. See GitHub

As for the solution, you haven't shared any of your Startup.cs file so I'm not sure what yours looks like. I'll share the relevant pieces of mine.

You'll need to add the service and the middleware.

The service:

services.AddGraphQLUpload()
  .AddGraphQL((options, provider) =>
  {
  ...
  });

If you're using a endpoints to map to a middleware, you'll need to add the UseGraphQLUpload middleware, then map your endpoint.

Example:

  app.UseGraphQLUpload<YourSchema>("/api/graphql", new 
  GraphQLUploadOptions {
    UserContextFactory = (ctx) => new GraphQlUserContext(ctx.User)
  });
  app.UseEndpoints(endpoints =>
  {
    // map HTTP middleware for YourSchema at path api/graphql
    endpoints
      .MapGraphQL<YourSchema, GraphQLMiddleware<YourSchema>>("api/graphql")
      .RequireAuthorization();
    ...
    // Additional endpoints
    ...
   }

Everything else looks fine to me.