Javascript ternary operator in a spreadsheet fetch

Question

I have this code but I would like to add a ternary operator so that if the cell from the spreadsheet is empty it is replaced by an empty space and thus avoid 'Cannot destructure property 'v' of 'object null' as it is null'

(async () => {
  const data = JSON.parse(
      (
        await (
          //we select the columns we want to use where the box in column C is checked
          await fetch(
            "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/gviz/tq?&sheet=users&tq=Select%20A,B,C,D,E,F,G,H,I,J,K%20WHERE%20C=true"
          )
        ).text()
        //we remove the first 47chars and last 2 to remove the wrapping text
      ).slice(47, -2)
    )["table"],
    products = data["rows"].map((row) =>
      row["c"].reduce(
        (product, {
          v: value
        }, index) => ({
          ...product,
          [data["cols"][index]["label"]]: value
        }), {}
      )
);
...

I tried to add it myself but it does not work, I am not familiar with javascript and do not get why it doesn't work. Here's what I tried:

[data["cols"][index]["label"]]: value ? value : ' ' ;

Any help welcome

EDIT to add link to codepen: https://codepen.io/Dendenzilla/pen/jOxvMZQ

Answer 1

OP asked me for assistance in a comment.

Problem Defined

OP's code queries a Google Sheet for table data which is returned as JSONP. This would usually be processed using the Google Sheets API, but in this case we have custom code doing it. This code parses the JSONP into plain JSON, converts it to a sheet object, and finally transforms it into an array of product items that is used to build the GUI. This works, but sometimes a product item return null values, which causes an error.

The Solution

The values are usually strings, but Google Sheets also has number, Boolean, and other data types. So we need to set null and undefined, but not zero and false, to an empty string. And the test can be done using value === null || value === undefined or the shorthand version value == null. See this SO question for details.

And we add this test to the transformation so that no product items have null values. See the example snippet.

Other Problems

Though not part of the question, the code could be greatly simplified and improved.

The code is very monolithic and difficult to follow. Needs to be broken up into smaller chunks.

The code uses property names that are incompatible with object dot notation. The bracket notation results in a lot of extra code.

The code creates large chunks of HTML which it appends to the page and then backfills with data. Template Literals would eliminate much of this extra code.

The page uses entirely custom css rather than one of the popular libraries like Bootstrap. And this makes it harder for others to understand and maintain.

Code Snippet

The section of the code marked ETL shows how to convert the property keys to camelCase for dot notation and convert null values to empty strings. Yet, this changes the product item schema as shown in the example data. The GUI code will need t be reworked to use the new property names, but this will also eliminate the need for much of that code.

  // Query Google Sheet for data
  // The query returns JSONP which must be converted to a JSON string and then a table object
  
  const query = "https://docs.google.com/spreadsheets/d/1tlqyTx48A-4CfgggWbpTbGd8SXCpCSO4UMV7Q0_ogJg/gviz/tq?&sheet=users&tq=Select%20A,B,C,D,E,F,G,H,I,J%20WHERE%20C=True";

  const json = await fetch( query )
    .then(response => response.text())
    .then(text => text.slice(47,-2))
    .catch(error => { throw error; });
  
    const data = JSON.parse(json);
    
  // ETL - The table columns and rows are transformed into an array of product items

    const products = data.table.rows.map(row => 
     data.table.cols.reduce((product, col, index) =>  {
         product[toCamelCase(col.label)] = getValue( row.c[index].v );
       return product;
     }, {} )
  );
  
// Helpers

// see: https://stackoverflow.com/q/5515310/943435
// optionally add other conversions and validations here
function getValue(value) { 
    return value === null || value === undefined ? "" : value;
}

// see: https://stackoverflow.com/a/2970667/943435
// used to create camelCase keys that are compatible with JavaScript dot notation
function toCamelCase(str) {
  return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) {
    if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
    return index === 0 ? match.toLowerCase() : match.toUpperCase();
  });
}

Example Data Output

[
  {
    "title": "Neutrik XLR Female",
    "category": "Connectors",
    "productOnSalePage": true,
    "oldPrice": "9.24",
    "newPrice": "3.00",
    "units": "per unit",
    "subTitle": "5 pole chassis connector",
    "description": "Black housing & Gold contacts",
    "imageLink": "https://www.belram.be/wp-content/uploads/2022/10/nc5md-l-b-1.jpg",
    "productLink": "https://www.belram.be/product/connectors/audio/xlr/neutrik-nc5md-l-b-1-5-pole-male-d-size/"
  },
... more elements
]