Updating a Google Sheet row and adding a row at the same time

In the shipping schedule app I am building, it is sometimes necessary to change a shipping date. This was handled by putting a note in the original record that the shipping date was being moved and then adding another record which is a copy of the original record with the new shipping date.

When a change is made to the table and the "save changes" button is clicked, is there a way to tell if the shipping date has been changed and if so, is there a way to update the current row with an automatic note saying the shipping date has been changed, and add a new record that has the new shipping date?

Hey @tomm!

The .changeSet property on your table contains only those cells which actually have a change so I imagine that's a good place to start. At the moment you write back to your DB you could check if that property exists for any rows and then modify them/add new rows.

Out of curiosity:

  • Do you already have a workflow in mind for how you'd create the new row?
  • Would the old row still get the other updates besides being marked as having a new shipping date?
  • What kind of DB are you using?

Hello @Kabirdas Henry

If the row update includes a new shipping date, I would like to insert the new record with the new shipping date immediately.

The old row may have other updates, but most likely it will just be the new shipping date.

I am currently using Google Sheets.

Thanks,
Tom

Got it, do you have a primary key that you're using for bulk updates, or are you doing it one row at a time by some unique combination of columns?

I have an ID field I am using as primary key.

Alright, I think the basis of this is that you'll want an update query and a query that appends new rows. Roughly, you can write all the handling of how you want to recognize the new shipping date in a JS query with something like this:

const changeSet = yourTable.changeSet;
const currentData = yourTable.data;
const rowUpdates = [];
const newRows = [];

// the .changeSet property is indexed with i in a way that matches your underlying table data
// so you can pull i from changeSet and access the corresponding row with yourTable.data[i]
for(let i in changeSet){
   if(changeSet[i].shippingDate){
      //overwrite column that indicates new shipping date
      const updatedRow = {...currentData[i], hasNewShippingDate: true}; 
     
      //generate a new id, using the uuid library here but it should depend on how you're currently generating ids
      const id = uuid.v4();

      //build new row with full changeset
      const newRow = {...currentData[i], ...changeSet[i], id};
      
      //add both an update and a new row
      rowUpdates.push(updatedRow);
      newRows.push(newRow);
   } else {
      //build updated row with full changeset
      const updatedRow = {...currentData[i], ...changeSet[i]};
      
      //only add an update
      rowUpdates.push(updatedRow);
   }
}

//two separate queries - one to handle updates and one to handle inserts since sheets doesn't support upserts
bulkUpdateQuery.trigger({additionalScope: {rowUpdates}});
appendRowsQuery.trigger({additionalScope: {newRows}});

Let me know if that helps or brings up any questions!