# 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](/sheet-linker/sheet-to-json.md#get-command) => Calls when needed for JSON
2. [Edit Command](#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
```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:

```html
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
```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](https://script.google.com/macros/s/AKfycbxHLP8yjErEQhU-BLTMJE6i0MllRpEPF2qTcVx0xNoe7Ur34dPqIG8bh4ay3CwUoQL4/exec) to see the output JSON. It will be like this:<br>

<figure><img src="/files/IG5Ltft6sbPIsqYLxkyu" alt=""><figcaption></figcaption></figure>

#### 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
```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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://muhammad-taha-bin-farooq.gitbook.io/sheet-linker/sheet-to-json.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.