Update Google Sheet Row based on ID value (I use Javascript to insert the payload to my sheet)

Question

I wanted to update the entire row with new submitted data based on ID. I have a form on my page that sends the data to my sheet using Javascript.

Here's the simple script that sends based on input ID:

<script>
            const scriptURL = "https://script.google.com/macros/s/AKfycxxxxxx/exec";
            const form = document.forms['google-sheet']
          
            form.addEventListener('submit', e => {
              e.preventDefault()
              fetch(scriptURL, { method: 'POST', body: new FormData(form)})
                .then(response => document.getElementById("conf").submit() )
                .catch(error => console.error('Error!', error.message))
            })
</script>

Here's the app script I got online:

var sheetName = 'Sheet1' var scriptProp = PropertiesService.getScriptProperties()

    function intialSetup () {
      var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
      scriptProp.setProperty('key', activeSpreadsheet.getId())
    }

    function doPost (e) {
      var lock = LockService.getScriptLock()
      lock.tryLock(10000)

      try {
        var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
        var sheet = doc.getSheetByName(sheetName)

        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
        var nextRow = sheet.getLastRow() + 1

        var newRow = headers.map(function(header) {
          return header === 'timestamp' ? new Date() : e.parameter[header]
        })
        
        
            
            sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
            return ContentService
            .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'success', 'row': newRow })+ ")")
            .setMimeType(ContentService.MimeType.JAVASCRIPT);
            
      }

      catch (e) {
        return ContentService
          .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'error', 'error': e })+ ")")
          .setMimeType(ContentService.MimeType.JAVASCRIPT)
      }

      finally {
        lock.releaseLock()
      }
    }

I was able to post the data on my sheet, but I wanted to update the entire row if the ID is found, and insert if ID is not found:

Tanaike Tanaike · Accepted Answer · 2021-06-14T02:30:55

I believe your goal as follows.

When the form is submitted, you want to search id from the column "A" of "Sheet1".
When the submitted ID is existing in the column "A" of "Sheet1", you want to update the same row with the submitted values.
When the submitted ID is not existing in the column "A" of "Sheet1", you want to append the submitted values.

In this case, how about the following modification?

From:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})

To:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header];
});
var find = sheet.getRange(1, 1, sheet.getLastRow()).createTextFinder(newRow[0]).matchEntireCell(true).findNext(); // Added
sheet.getRange(find ? find.getRow() : nextRow, 1, 1, newRow.length).setValues([newRow]); // Added

Note:

When you modified the Google Apps Script, please modify the deployment as new version. By this, the modified script is reflected to Web Apps. Please be careful this.
You can see the detail of this at the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
This answer supposes that your current script works fine. Please be careful this.
In your situation, I thought that matchEntireCell(true) might be suitable for TextFinder. So I updated above script. Ref

Update Google Sheet Row based on ID value (I use Javascript to insert the payload to my sheet)

1 Answers

From:

To:

Note:

References: