How can I import JSON to Google Sheets from Godot via Apps Script so my constants are in the first frozen column?

Question

I'm currently following this tutorial / resource: https://github.com/amzker/Gsheet_Godot This outputs data sent from a Godot program to a sheet that looks like this,

GreekAlphabet	WhatHungryDogDo	BananasGoodFor	HowManyWords
Alpha	The	Bananas	These
Beta	Hungry	Are	Are
Delta	Dog	Nice	Four
Gamma	Eats	Snacks	Words

My goal is to upload data to a Google Sheet from Godot in a format that resembles this,

GreekAlphabet	Alpha	Beta	Delta	Gamma
WhatHungryDogDo	The	Hungry	Dog	Eats
BananasGoodFor	Bananas	Are	Nice	Snacks
HowManyWords	These	Are	Four	Words

The relevant apps script code is as follows:

function json(sheetName) {
  const spreadsheet = SpreadsheetApp.openById("1h_KlXz9IWt2MtQQWYUSk4FJbIr02MbfXWU3ZRqY7U3I") //CHANGE WITH YOUR SHEET ID ( see url of you sheet d/)
  const sheet = spreadsheet.getSheetByName(sheetName)
  const data = sheet.getDataRange().getValues()
  const jsonData = convertToJson(data)
  return ContentService
        .createTextOutput(JSON.stringify(jsonData))
        .setMimeType(ContentService.MimeType.JSON)
}
function convertToJson(data) {
  const headers = data[0]
  const raw_data = data.slice(1,)
  let json = []
  raw_data.forEach(d => {
      let object = {}
      for (let i = 0; i < headers.length; i++) {
        object[headers[i]] = d[i]
      }
      json.push(object)
  });
  return json
}
function doGet(params) {
  const sheetname = params.parameter.sheetname
  return json(sheetname)
}

function doPost(params) {
  const datee = params.parameter.date
  const timee = params.parameter.time
  const catee = params.parameter.cate
  const amounte = params.parameter.amount
  const desce = params.parameter.desc
  const sheetname = params.parameter.sheetname

  
  if(typeof params !== 'undefined')
  Logger.log(params.parameter);

  var ss  = SpreadsheetApp.openById("1h_KlXz9IWt2MtQQWYUSk4FJbIr02MbfXWU3ZRqY7U3I") //CHANGE WITH YOUR SHEET ID ( see url of you sheet d/)
  var sheet = ss.getSheetByName(sheetname)
  var Rowtoenter = sheet.getLastRow()+1
  sheet.appendRow([datee,timee,catee,amounte,desce])
  

/* 
  var datecol = sheet.getRange(Rowtoenter,1)
  var timecol = sheet.getRange(Rowtoenter,2)
  var catecol = sheet.getRange(Rowtoenter,3)
  var amountcol = sheet.getRange(Rowtoenter,4)
  var descol = sheet.getRange(Rowtoenter,5)
  
  datecol.setValue(datee)
  timecol.setValue(timee)
  catecol.setValue(catee)
  amountcol.setValue(amounte)
  descol.setValue(desce)
*/
  
}

When the data leaves Godot, it's in the form of a URL with a string of parameters appended.

date=TODAY&time=NOW&cate=CATE&amount=AMOUNT&desc=DESC&flibble=SPONDS&sheetname=Sheet1

Once it reaches Sheets, it's converted into the first kind of table, where I really want it to appear as the second kind of table. =/

I'm trying to wade through the source and read through the Docs (https://developers.google.com/apps-script/reference/spreadsheet/sheet) but I'm very new to working with JSON and I'm finding it hard to get a starting point.

Any thoughts appreciated!

(https://i.sstatic.net/EY0Yl.png) (https://i.sstatic.net/kASx9.png)

Answer 1

const spreadsheet = SpreadsheetApp.openById("1h_KlXz9IWt2MtQQWYUSk4FJbIr02MbfXWU3ZRqY7U3I")

/** Requires sheet to already be in column-header format */
function convertToJson(data) {
  return data.flatMap(([header, ...rowData]) => ({ [header]: [...rowData] }))
}

function json(sheetName) {

  const data = spreadsheet.getSheetByName(sheetName).getDataRange().getValues()
  const jsonData = convertToJson(data)

  return ContentService
        .createTextOutput(JSON.stringify(jsonData))
        .setMimeType(ContentService.MimeType.JSON)

}

function doGet(params) {
  return json(params.parameter.sheetname)
}

function doPost(params) {

  const { date, time, cat, amount, desc, sheetname } = params.parameter

  const sheet = spreadsheet.getSheetByName(sheetName)
  const entryColumn = sheet.getLastColumn()+1  

  const columnData = [date, time, cat, amount, desc].map(item => [item])
  sheet.getRange(1, entryColumn, columnData.length, 1).setValues(columnData)
  
}

---

If your sheet(s) are still in the "top row as header" format, you can run:

function convertSheet() {

  const sheet = spreadsheet.getSheetByName(`**YOUR_SHEET**`)

  const sheetValues = sheet.getDataRange().getValues()
  const convertedValues = sheetValues[0].map((_, index) => sheetValues.flatMap(row => row[index]))

  sheet.setFrozenRows(0)
  sheet.setFrozenColumns(1)

  sheet.getDataRange().clearContent()
  sheet.getRange(1, 1, convertedValues.length, convertedValues[0].length).setValues(convertedValues)

}