CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
geeksified
geeksified

Reputation: 61

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

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:

enter image description here

Upvotes: 1

Views: 882

Answers (1)

Tanaike
Tanaike

Reputation: 201713

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

References:

Upvotes: 3

Related Questions