Cannot Add Value From Web App To Existing Google Sheet Row

1
votes

Hey I am trying to add more values (from a submit form) to a specified existing row. But I got Console error:

Uncaught at processPk1

Here's my .html code:

onsubmit="handleFormSubmit(this)         //got this onsubmit in my form

<script>
function handleFormSubmit(formObject) {
  google.script.run.processPk1(formObject);
  document.getElementById("myForm").reset();
}
</script>

Here's my .gs code:

function processPk1(formObject) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("Sheet1");
var data = ws.getRange(2, 1, ws.getLastRow()-1, 1).getValues();

var codeList = data.map(function(r){return r[0].toString(); });
var position = codeList.indexOf(formObject.code);    //this is to find the specified existing row position

ws.getRange(position,13).setValue(formObject.pdca);
ws.getRange(position,15).setValue(formObject.teamwork);
ws.getRange(position,17).setValue(formObject.strive);
ws.getRange(position,19).setValue(formObject.cnc);
}

Your response would be really appreciated. Thank you in advance!

EDIT

What I'm trying to do is user could submit a "code" that exist in the Google Sheet, if it found then user could add the "formObject.pdca, formObject.teamwork, etc" on the same row as the "code".

Here's my sample spreadsheet:

https://docs.google.com/spreadsheets/d/15NboiL7aIkUFq267t3yhy9hLRGrdbHI2jAlrIq54SvM/edit?usp=sharing

1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

3
votes

How about this answer?

Modification points:

  • In your script, when the value of formObject.code is not existing in the column "A" in "Sheet1", position is -1. By this, an error occurs. I thought that this might be the reason of your issue.
  • Also, when the same value for the column "A" is given, other values are not the same row of the value of column "A". From your script, I thought that you might want to put the same rows with the value of column "A".

When above points are reflected to your script, how about the following modification?

Modified script:

Google Apps Script side:

ws.getRange(position,13).setValue(formObject.pdca);
ws.getRange(position,15).setValue(formObject.teamwork);
ws.getRange(position,17).setValue(formObject.strive);
ws.getRange(position,19).setValue(formObject.cnc);

if (position > -1) {
  position += 2;
} else {
  position = ws.getLastRow() + 1;
  ws.getRange(position,1).setValue(formObject.code);
}
ws.getRange(position,13).setValue(formObject.pdca);
ws.getRange(position,15).setValue(formObject.teamwork);
ws.getRange(position,17).setValue(formObject.strive);
ws.getRange(position,19).setValue(formObject.cnc);
  • In this modification, when formObject.code is not existing in the column "A", the values are appended to the sheet.

HTML and Javascript side:

google.script.run is run with the asynchronous process. So the following modification might be suitable.

google.script.run.processPk1(formObject);
document.getElementById("myForm").reset();

google.script.run.withSuccessHandler(() => document.getElementById("myForm").reset()).processPk1(formObject);

Note:

  • When you use Web Apps as the URL of exec and modified the script, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

References:

Added:

If you want to update the rows only when formObject.code is found at the column "A", how about the following modification?

Google Apps Script side:

ws.getRange(position,13).setValue(formObject.pdca);
ws.getRange(position,15).setValue(formObject.teamwork);
ws.getRange(position,17).setValue(formObject.strive);
ws.getRange(position,19).setValue(formObject.cnc);

if (position > -1) {
  position += 2;
  ws.getRange(position,13).setValue(formObject.pdca);
  ws.getRange(position,15).setValue(formObject.teamwork);
  ws.getRange(position,17).setValue(formObject.strive);
  ws.getRange(position,19).setValue(formObject.cnc);
}
  • By this modificction, when formObject.code is NOT found at the column "A", the script do nothing.