Update/edit google sheets row values from html input

Question

I have an html form where the client's data is inserted in and it appends row with the values on to a google sheet.

In the form, there's a field that searches and returns the clients data when searching for a specific value (id number).

function getID(IDsearch){
  var ws = SpreadsheetApp.getActiveSheet();
  var data = ws.getRange(3, 1, ws.getLastRow(), 36).getValues();

  var dataInput = data.map(function(r){return r[7];});  
  var position = dataInput.indexOf(IDsearch);
  var dataArray = ws.getRange(position+3, 1, 1, 36).getValues();

  if(position > -1){
    return dataArray;
  } else {
    return position;
  }

}

After this runs, all the input fields in the form are populated with the data from that row. I need to edit the values in the form and when submit it should overwrite/update the existing row with that id number.

In google sheets documentation, I've found the spreadsheets.values.update method, but I cannot figure this out. I'm pretty new in this and any help would be appreciated.

Thanks everyone!

Can I ask you about the sample input and output values for your function of getID you expect? — Tanaike
– Tanaike, Commented Feb 2, 2020 at 22:48
The getID function finds the index of the row where the ID number is and returns the row data in an array. — dianadf
– dianadf, Commented Feb 2, 2020 at 23:02
Thank you for replying. In your script, I think that the script works like your replying. Under this situation, can I ask you about your current issue? I cannot correctly understand about I need to edit the values in the form and when submit it should overwrite/update the existing row with that id number.. About this, I have to apologize for my poor English skill. — Tanaike
– Tanaike, Commented Feb 2, 2020 at 23:08
No problem :) So basically, that script is working fine. It returns the row data and populates the html form. Now, what I need is to be able to save the editing done in the form to that data. Currently, whenever I submit the form with the edited data, it creates a new row with the same ID. I need to edit the row and not create a new one. — dianadf
– dianadf, Commented Feb 2, 2020 at 23:18
Thank you for replying. I have apologize again. About what I need is to be able to save the editing done in the form to that data. Currently, whenever I submit the form with the edited data, it creates a new row with the same ID. I need to edit the row and not create a new one., I cannot image the vision of it. In your case, should you show the form script instead of your current script in your question? — Tanaike
– Tanaike, Commented Feb 2, 2020 at 23:28

Tanaike · Accepted Answer · 2020-02-03 22:46:22Z

2

You want to achieve the following flow.
1. Input "ID" to id="insertID" and click "Search by ID".
2. Show the values from Spreadsheet by searching "ID".
3. Edit the values of id="name" and id="ID".
4. When "Save data" is clicked, you want to update the values on the Spreadsheet.

From your replying, shared Spreadsheet and script, I could understand like above. If my understanding is correct, how about ths following modification? Please think of this as just one of several possible answers.

Modification points:

In your case, processForm at Google Apps Script side is required to be modified.
- Search the row using formObject and overwrite the values of cells.

Modified script:

When your script is modified, please modify processForm at Google Apps Script side as follows. I remove the Spreadsheet ID from the URL. So please set it, before you test the script.

function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/###/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Database");

  // I added and modified below script.
  var ranges = ws.getRange(4, 2, ws.getLastRow() - 3, 1).createTextFinder(formObject.ID).findAll();
  if (ranges.length > 0) {
    for (var i = 0; i < ranges.length; i++) {
      ranges[i].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);
    }
  } else {
    ws.appendRow([formObject.name, formObject.ID]);
  }
}

In this modification, when the same IDs are existing, all rows of the same IDs are overwritten. For example, if you want to modify the 1st one, please modify to ranges[0].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);.

Reference:

Class TextFinder

edited Feb 3, 2020 at 22:46

answered Feb 3, 2020 at 22:27

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dianadf Over a year ago

This is great! It does update the row values. Now what is missing is the fact that I would like to have both possibilities when pressing "save data", i.e., create new row if ID doesn't exist in the sheet or update row if ID exists in the sheet.

Tanaike Over a year ago

@dianadfonseca Thank you for replying. I apologize for the incomplete answer. For your replying, I updated my answer. Could you please confirm it? If that was not the direction you want, I apologize.

dianadf Over a year ago

Perfect! This is it! Thanks for your help :)

Tanaike Over a year ago

@dianadf Thank you for replying. I'm glad your issue was resolved. I could correctly understand about your issue by your provided the sample Spreadsheet including the script. Thank you, too.

Cooper · Accepted Answer · 2020-02-03 00:24:06Z

0

Try this:

function getID(IDsearch){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();//dont know what the sheet is
  var rg=sh.getRange(3,1,sh.getLastRow()-2,36);
  var data=rg.getValues();
  var idA=sh.getRange(3,8,sh.getLastRow()-2,1).getValues().map(function(r){return r[0];});//it looked like column 8 was your id column
  var idx=idA.indexOf(IDsearch);
  if(idx>-1) {
    return ws.getRange(pos + 3,1,1,36).getValues()[0];//flattened the row to a 1d array
  }else{
    return idx;
  }
}

answered Feb 3, 2020 at 0:24

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

2 Comments

dianadf Over a year ago

Thanks for the reply. How exactly does this update the edited row? Pls check the sample link above for a better understanding.

Cooper Over a year ago

It doesn't actually update the row it simply finds and returns the row. My answer came approximately 16 hours before your last question update and that seemed to be what you were trying accomplish. Or perhaps I just misunderstood the question. That happens. I apologize.

Community · Accepted Answer · 2020-06-20 09:12:55Z

@dianadfonseca, as @Tanaike points out, without more detail about your data structure, people will be speculating in order to answer your question. As I will be...

Please read the following answer, and tailor it to your needs if it works for you.

Example:

function getRow(id){
  
  var ws = SpreadsheetApp.getActiveSheet();
  
  // Number of headers to skip
  var numHeaders = 2;
  
  // the starting row
  var startRow = numHeaders + 1;
  
  // The column where the IDs are is known
  var idCol = 8;
  
  // The number of rows with data not headers
  var numRows = ws.getDataRange().getLastRow() - numHeaders;
  
  // An array with the ids to find a match in
  // getRange() returns a 2D array, so you can transpose it to flatten it
  var ids = ws.getRange(startRow,idCol,numRows).getValues();
  ids = transpose(ids)[0];
  
  // Get the index where id matches in ids
  var row = ids.indexOf(id);
  
  // If there's a match
  if(row > -1){
    
    // Correct row indexing 
    row = row + startRow;
  } 
  
  return row;
}

function updateRow(row,data){
  var ws = SpreadsheetApp.getActiveSheet();
  
  // The column for each property is known
  var propertyOneCol = 1;

  // Update property using setValue()
  ws.getRange(row,propertyOneCol).setValue(data.propertyOne);
  
  // And so on...
  
}

// Transpose to avoid looping through the array
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

You can take a look the spreadsheet used for this example here with its bound script to play around.

Here is the function I used for testing

function test(){
  
  // You are receiving this from your form
  var data = {"propertyOne":"Juan","propertyTwo":20, "id":123467};
  var id = data.id;
  updateRow(getRow(id),data);
}

Collectives™ on Stack Overflow

Update/edit google sheets row values from html input

3 Answers 3

Modification points:

Modified script:

Reference:

4 Comments

2 Comments

Example:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Modification points:

Modified script:

Reference:

4 Comments

2 Comments

Example:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related