Sheet To JSON

Now, let's move on to the crucial step of converting the sheet into JSON format. For this task, I utilized App Script. If you're unfamiliar with it, App Script is a scripting language based on JavaScript. It allows you to fetch and modify data from the sheet. Once the script is ready, you can deploy it for external use. This is a great starting point if you're new to this process.

In this project, two primary commands were utilized:

1. Get Command => Calls when needed for JSON

2. Edit Command => Calls when we modify sheet

Get Command:

This command is integral to the entire system as it retrieves data. Executing the following code will yield the fetched data:

```javascript
function FetchData() 
{
  var startingRow = 2
  var end = SpreadsheetApp.getActiveSheet().getLastRow()
  var lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn() - 1
  let lastColumnAplhabet = GetColumnByIndex(lastColumn)

  var titles = SpreadsheetApp.getActiveSheet().getRange("Sheet1!A1:" + lastColumnAplhabet + "1").getValues()[0]

  let data = SpreadsheetApp.getActiveSheet().getRange("Sheet1!A"+ startingRow + ":" + lastColumnAplhabet + "" + end).getValues()

  // rows iteration
  for (var i = 0; i < data.length; i++)
  {
    // column iteration
    columnIndex = 0
    for(var j = 0; j < data[i].length; j++)
    {
      var cell = data[i][j]
      if(cell.toString() == "CellImage")
      {
        let value = SpreadsheetApp.getActiveSheet().getRange(GetColumnByIndex(columnIndex) + "" + startingRow).getFormula()

        var regEx = /"(.*)"/gm
        var url = regEx.exec(value)[1]

        cell = url
      }
      columnIndex++

      data[i][j] = cell
    }
    startingRow++
  }

  let output = []
  for (var i = 0; i < data.length; i++)
  {
    let obj = {}
    for(var j = 0; j < data[i].length; j++)
    {
      obj[titles[j]] = data[i][j];
    }
    output.push(obj)
  }

  return output
}

function GetColumnByIndex(column)
{
  return String.fromCharCode(97 + column).toUpperCase()
}
```

In the aforementioned process, I retrieve all the data from the sheet. If the value corresponds to a Cell Image, I use a regular expression (regex) to extract the actual download URL. Here's how it works:

https://drive.google.com/uc?export=download&id=YOUR IMAGE ID

Once all the data has been fetched and filtered, I push it to the output and return it. The output is an array of dictionaries, where each dictionary consists of a key-value pair. The key represents the title, and the value represents the corresponding data.

After this Get Function is called:

```javascript
function doGet()
{
  Logger.log(JSON.stringify(FetchData()))  
  return ContentService.createTextOutput(JSON.stringify(FetchData())).setMimeType(ContentService.MimeType.JSON);
}
```

This is the output JSON. You can run this to see the output JSON. It will be like this:

Edit Command:

This command is primarily utilized to alter the sheet or perform a specific action when an edit is made to the sheet. I've used it to apply a background color to a cell in the sheet, which allows the designer to identify the color. Here's the corresponding code:

```javascript
function onEdit(editData)
{
  ChangeColor(editData)
}

function ChangeColor(editData)
{
  range = editData.range
  var rows = range.getNumRows()
  var columns = range.getNumColumns()
  var colors = []

  for (var i = 1; i <= rows; i++) 
  {
    var row = []
    for (var j = 1; j <= columns; j++) 
    {
      if(!isValidColor(range.getCell(i,j).getValue().toString()))
      {
        row.push("")
      }else
      {
        row.push(range.getCell(i,j).getValue())
      }
    }
    colors.push(row)
  }
  range.setBackgrounds(colors)
}

function isValidColor(str) {
    return str.match(/^#[a-f0-9]{6}$/i) !== null;
}
```

To provide some context, I examine each cell to determine if its value is a valid HexCode. If it is, I change the cell's color. If it's not, I simply move on to the next cell.