JSON - Get value of row's next column value-CodePudding

This is the JSON structure of rows I get exported from a Google Sheets:

[
    {
        "COLUMN1HEADER": "V1",
        "COLUMN2HEADER": "response 1"
    },
    {
        "COLUMN1HEADER": "V2",
        "COLUMN2HEADER": "response 2"
    },
    {
        "COLUMN1HEADER": "V3",
        "COLUMN2HEADER": "response 3"
    }
]

The user input is equal to a COLUMN1HEADER value (for example, let input = 'V2') and from this string value I'm struggling to get the COLUMN2HEADER value ("response 2") as return.

CodePudding user response：

Use Array.filter(), like this:

function test() {
  const searchValue = 'V2';
  const jsonData = `[
    {
      "COLUMN1HEADER": "V1",
      "COLUMN2HEADER": "response 1"
    },
    {
      "COLUMN1HEADER": "V2",
      "COLUMN2HEADER": "response 2"
    },
    {
      "COLUMN1HEADER": "V3",
      "COLUMN2HEADER": "response 3"
    }
  ]`;
  const data = JSON.parse(jsonData);
  const result = getFieldValue_({ data: data, searchKey: 'COLUMN1HEADER', searchValue: searchValue, resultKey: 'COLUMN2HEADER' });
  console.log(result);
}


function getFieldValue_({ data, searchKey, searchValue, resultKey } = {}) {
  const matches = data.filter(obj => obj[searchKey] === searchValue);
  if (!matches.length) {
    return null;
  }
  const firstMatch = matches[0];
  return firstMatch[resultKey];
}

CodePudding user response：

You could created the JSON like this:

function getData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getDataRange().getValues();
  Logger.log(JSON.stringify( {"values":vs}));
}

{values:[["COLUMN1HEADER","COLUMN2HEADER"],["V1","response 1"],["V2","response 2"],["V3","response 3"]]}

Then you would have an output similar to what Sheets API provides and you would be able to use setValues() to drop it right back into a spreadsheet.