Search code examples
google-apps-scriptgoogle-sheets

Compare two json array and update dataSheet with setValues updating empty rows

I have a datasheet that contains the ordered data. The sheet consists of thousands of rows identified by the order id. In the second column I have all the data grouped as json string. The new values are added starting from the last row.

This operation works.

My problem starts when I have to update the changes of some values of ids.

The source where I parse data, both for append and update is a json api.

This is my simulation dataSheet:

Start dataSheet

A B C D E F G
1 string shipped timestamp id countitems destination delivery
2 {"id":"x11","shipped":null,"countItems":"21","destination":"Paris","delivery":null} x11 21 Paris
3 {"id":"x167","shipped":null,"countItems":"3","destination":"Lyon","delivery":null} x167 3 Lyon
4 {"id":"x89","shipped":null,"countItems":"8","destination":"Berlin","delivery":null} x89 8 Berlin
5 {"id":"x9811","shipped":null,"countItems":"2","destination":"Madrid","delivery":null} x9811 2 Madrid
6 {"id":"x73","shipped":null,"countItems":"9","destination":"Liverpool","delivery":null} x73 9 Liverpool

This is my current procedure:

1) Apply a filter to the json source, selecting the elements where "shipped" is yes.

// My API is for business use, so I can't share URL and token.
// Request and download work perfectly

const loadShipped = () => {return jsonApi().filter(k=>k.shipped == "yes")}

I get this output

var shipped = [
 {"id":"x167","shipped":"yes","countItems":"3","destination":"Lyon","delivery":"22/09/2023"}
,{"id":"x9811","shipped":"yes","countItems":"2","destination":"Madrid","delivery":"27/09/2023"}
]

2) Create a json output of column B and apply a filter selecting the elements where "shipped" is null.

const jsDataSheet = () => {
  const colJS = SpreadsheetApp.getActiveSpreadsheet().getRange("B5:B")
                              .getDisplayValues().filter(col=>col[0]);
  const data = `[${colJS.filter(o=>o!='').join(',')}]`;
  const dataSheetJson = [...new Set(JSON.parse(data))];
  return dataSheetJson.filter(k=>!k.shipped)
}

I get this output

var toUpdate = [
 {"id":"x167","shipped":null,"countItems":"3","destination":"Lyon","delivery":null}
,{"id":"x9811","shipped":null,"countItems":"2","destination":"Madrid","delivery":null}
]

cons: The columns are not adjacent and usually the two json have different length and sort.

This is the script I'm using, is very slow, this is the problem for me, I have too much data to update and many cells to fill. For this situation I have reduced the number of elements.

const sheet = SpreadsheetApp.getActiveSpreadsheet();

    for(var i=0;i<toUpdate.length;i++){
      for(var j=0;j<shipped.length;j++){
        if(toUpdate[i].id == shipped[j].id){

          const time = Date.parse(new Date()) / 1000;
          const ship = shipped[j].shipped;
          const dDel = shipped[j].delivery;

          const newJS = 
          JSON.stringify({...toUpdate[i]
          ,timestamp:time
          ,shipped:ship
          ,delivery:dDel
          });

          sheet.getRange(i + 5,2).setValue(newJS)
          sheet.getRange(i + 5,3).setValue(ship)
          sheet.getRange(i + 5,4).setValue(time)          
          sheet.getRange(i + 5,8).setValue(dDel)
        }
      }
    }

This is final result

Final result

string shipped timestamp id countItems destination delivery
{"id":"x11","shipped":null,"countItems":"21","destination":"Paris","delivery":null} x11 21 Paris
{"id":"x167","shipped":null,"countItems":"3","destination":"Lyon","delivery":null} yes x167 3 Lyon 22/09/2023
{"id":"x89","shipped":null,"countItems":"8","destination":"Berlin","delivery":null} x89 8 Berlin
{"id":"x9811","shipped":null,"countItems":"2","destination":"Madrid","delivery":null} yes x9811 2 Madrid 27/09/2023
{"id":"x73","shipped":null,"countItems":"9","destination":"Liverpool","delivery":null} x73 9 Liverpool

The target is to update data once, update string rows and put the values into empty cells, without changing values already added.

shared google link

Solution

  • This should work:

    const shipped /*data from api*/ = [
  {
    id: 'x167',
    shipped: 'yes',
    countItems: '3',
    destination: 'Lyon',
    delivery: '22/09/2023',
  },
  {
    id: 'x9811',
    shipped: 'yes',
    countItems: '2',
    destination: 'Madrid',
    delivery: '27/09/2023',
  },
];
const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[DATA SHEET]'),
  dataRange = sheet.getDataRange(),
  data = dataRange.getDisplayValues(),
  idColIdx = 5 - 1,
  idJsonIdx = 2 - 1,
  shippedMap = shipped.reduce((m, c) => m.set(c.id, c), new Map());

data.forEach((row) => {
  const idx = row[idColIdx];
  if (shippedMap.has(idx)) {
    const thisShipped = shippedMap.get(idx),
      time = Date.parse(new Date()) / 1000,
      ship = thisShipped.shipped,
      del = thisShipped.delivery,
      thisUpdate = [
        JSON.stringify({
          ...JSON.parse(row[idJsonIdx]),
          timestamp: time,
          shipped: ship,
          delivery: del,
        }),
        ship,
        time,
        del,
      ];
    [2, 3, 6, 8].map((c) => c - 1).forEach((c, k) => (row[c] = thisUpdate[k]));
  }
});
dataRange.setValues(data);

    Mock Test:

    /*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
const shipped /*data from api*/ = [
  {
    id: 'x167',
    shipped: 'yes',
    countItems: '3',
    destination: 'Lyon',
    delivery: '22/09/2023',
  },
  {
    id: 'x9811',
    shipped: 'yes',
    countItems: '2',
    destination: 'Madrid',
    delivery: '27/09/2023',
  },
];
const data = [
    [
      '2',
      '{"id":"x167","shipped":null,"countItems":"3","destination":"Lyon","delivery":null}',
      '',
      '',
      'x167',
      '3',
      'Lyon',
    ],

    [
      '3',
      '{"id":"x89","shipped":null,"countItems":"8","destination":"Berlin","delivery":null}',
      '',
      '',
      'x89',
      '8',
      'Berlin',
    ],

    [
      '4',
      '{"id":"x9811","shipped":null,"countItems":"2","destination":"Madrid","delivery":null}',
      '',
      '',
      'x9811',
      '2',
      'Madrid',
    ],

    [
      '5',
      '{"id":"x73","shipped":null,"countItems":"9","destination":"Liverpool","delivery":null}',
      '',
      '',
      'x73',
      '9',
      'Liverpool',
    ],
  ],
  idColIdx = 5 - 1,
  idJsonIdx = 2 - 1,
  shippedMap = shipped.reduce((m, c) => m.set(c.id, c), new Map());

data.forEach((row) => {
  const json = JSON.parse(row[idJsonIdx])
  const idx = json.id;
  if (shippedMap.has(idx)) {
    const thisShipped = shippedMap.get(idx),
      time = Date.parse(new Date()) / 1000,
      ship = thisShipped.shipped,
      del = thisShipped.delivery,
      thisUpdate = [
        JSON.stringify({
          ...json,
          timestamp: time,
          shipped: ship,
          delivery: del,
        }),
        ship,
        time,
        del,
      ];
    [2, 3, 6, 8].map((c) => c - 1).forEach((c, k) => (row[c] = thisUpdate[k]));
  }
});
console.log(data);
    <!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>