The bottom line is that I need to first check the incoming data with what is already in the table. if no match is found, add the data (as new) to the next lines.

I try this way.

function onEdit(event) { var content = JSON.parse(event.postData.contents); var ss = event.source.getActiveSheet(); var r = event.source.getActiveRange(); if(r.getColumn() == 2){ //column B if(r.getValue() == content['order'].order_number) { var id = ss.getRange('B'+r.getRow()).getValue(); // order id from the column A ss.getRange('V'+r.getRow()).setValue(content['order'].status+' Время обновления статуса '+new Date()); } else{ row.push(new Date()); row.push(content['order'].order_number) row.push(content['order'].shipping_address.first_name+' '+content['order'].shipping_address.last_name) row.push(content['order'].billing_address.email) row.push(content['order'].total_line_items_quantity); var item = ""; for (var i=0;content['order'].line_items.length>i;i++){ item=item+content['order'].line_items[i].quantity+' -'+content['order'].line_items[i].name+','; } item=item.substring(0,item.length-1); row.push(item); row.push(content['order'].total) row.push(content['order'].payment_details.paid) row.push(content['order'].status) row.push(content['order'].payment_details.method_title) row.push(content['order'].shipping_address.address_1+','+content['order'].shipping_address.city+','+content['order'].shipping_address.country+','+content['order'].billing_address.phone+', ZIP:'+content['order'].billing_address.postcode) row.push(content['order'].shipping_methods) row.push(content['order'].note) row.push(content['order'].view_order_url) var ss = SpreadsheetApp.openById('sheet id') var sheet = ss.getSheetByName("Sheet1"); sheet.appendRow(row); result.result = 'ok'; } } }

If you simply add data to the table (everything in the code after the else), it works, and if it doesn’t work with the search :( Tell me how to combine the two functions in one macro (Search / Replace - and add this if there are no matches ).

  • It is not clear what you need. It seems that the problem can be solved in one line, but in the example, something really hurts a lot. Why in the onEdit question, and in the answer doPost ? What is a "macro"? In Google Apps Script, programs are called "scripts." Give a simpler example. Preferably with a table. - oshliaer pm
  • There is a certain data set (json) that comes to me in the table when creating an order (who ordered, when, the status of the order), and the data when updating the order (the same lines) ... so, before writing the data - I need it was to check whether there is for example an order 728 in the table, and if there is one, update the status of this order in the table. If during the check, such an order is not in the table - just add the data as new. - Eugen Dubrovin

1 answer 1

Solution found. Not exactly what I wanted. I had to make two macros (the first one to check the matches, the second one to add) and run them depending on the new order, or update the old one!

---- Update the old order.

 function doPost(event) { var content = JSON.parse(event.postData.contents); var order = content['order'].order_number; //colors if (content['order'].status == "pending") { var clr = '#eeeeee' } if (content['order'].status == "processing") { var clr = '#e5ff00' } if (content['order'].status == "on-hold") { var clr = '#0000ff' } if (content['order'].status == "completed") { var clr = '#00ff00' } if ((content['order'].status == "cancelled") || (content['order'].status == "refunded") || (content['order'].status == "failed")){ var clr = '#ff0000' } //colors var ss = SpreadsheetApp.openById("SPREADSHEET_ID"); // ID Вашей таблицы var sheet = ss.getSheetByName("Sheet1"); var dataRange = sheet.getDataRange(); var grid = dataRange.getValues(); grid.forEach(function (row, index) { if (row[1] == order) { var row = "i" + (index + 1); sheet.getRange(row).setValue(content['order'].status) sheet.getRange(row).setBackgroundColor(clr); } }); }

---- Adding a new order

 function doPost(request) { var content = JSON.parse(request.postData.contents); var row = []; row.push(new Date()); row.push(content['order'].order_number) row.push(content['order'].shipping_address.first_name+' '+content['order'].shipping_address.last_name) row.push(content['order'].billing_address.email) row.push(content['order'].total_line_items_quantity); var item = ""; for (var i=0;content['order'].line_items.length>i;i++){ item=item+content['order'].line_items[i].quantity+' -'+content['order'].line_items[i].name+','; } item=item.substring(0,item.length-1); row.push(item); row.push(content['order'].total) row.push(content['order'].payment_details.paid) row.push(content['order'].status) row.push(content['order'].payment_details.method_title) row.push(content['order'].shipping_address.address_1+','+content['order'].shipping_address.city+','+content['order'].shipping_address.country+','+content['order'].billing_address.phone+', ZIP:'+content['order'].billing_address.postcode) row.push(content['order'].shipping_methods) row.push(content['order'].note) row.push(content['order'].view_order_url) var ss = SpreadsheetApp.openById('SPREADSHEET_ID') var sheet = ss.getSheetByName("Sheet1"); sheet.appendRow(row); result.result = 'ok'; return ContentService.createTextOutput(JSON.stringify(result)) .setMimeType(ContentService.MimeType.JSON); }