CODEWITHSUNDEEP
javascriptgoogle-apps-script
djalmafreestyler
djalmafreestyler

Reputation: 1909

How to update all columns of a row at once using spreadsheet on Google Apps Script?

I read some other similar questions but I couldn't understand it how to do it on my code.

I have a spreadsheet that will be filled with an app, I am using appendRow to add rows but now I need to update the entire row with new array of data, if the variable pid(Código) from the row I am receiving exists on the spreadsheet, I need to update it, not add a new row.

My Spreadsheet

    function doGet(request) {


      var sheet = SpreadsheetApp.openById("DOCUMENT_ID");

      var data = sheet.getActiveSheet().getDataRange().getValues();

      var updateIndex;


      try{

        var pid = request.parameter.pid;
        var nome = request.parameter.nome;
        var desc = request.parameter.desc;
        var marca = request.parameter.marca;
        var tipo = request.parameter.tipo;
        var preco = request.parameter.preco;
        var ativado = request.parameter.ativado;

        var rowData = [pid, nome, desc, marca, tipo, preco, ativado];

         // loop through all rows to check the column "pid" has the value of variable "pid"

          for(var i = 1; i < data.length; i++){

            if(data[i][0] == pid){
              updateIndex = i;
            }


          }

      // Update the row here with "rowData"? 
      sheet.



  } catch(e){

    console.log(e);

  }

  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType);



}

Upvotes: 0

Views: 1238

Answers (3)

Jorge Forero
Jorge Forero

Reputation: 1

My solution:

function doGet(request) {

      // Modified
      var sheet=SpreadsheetApp.openById("DOCUMENT_ID").getActiveSheet();          
      var data=sheet.getDataRange().getValues();
      // 

      var updateIndex;
      try{
        var pid=request.parameter.pid;
        var nome=request.parameter.nome;
        var desc=request.parameter.desc;
        var marca=request.parameter.marca;
        var tipo=request.parameter.tipo;
        var preco=request.parameter.preco;
        var ativado=request.parameter.ativado;
        var rowData=[pid, nome, desc, marca, tipo, preco, ativado];
          for(var i=1; i < data.length; i++){
            if(data[i][0] == pid){
              updateIndex=i;
              break;
            }
          }

         // Modifed
         sheet.getRange(updateIndex + 1, 1, 1, rowData.length).setValues( [rowData] );
         //

  } catch(e){
    console.log(e);
  }
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType);
}

Upvotes: 0

Cooper
Cooper

Reputation: 64042

function doGet(request) {
      var sheet=SpreadsheetApp.openById("DOCUMENT_ID");
      var data=sheet.getActiveSheet().getDataRange().getValues();
      var updateIndex;
      try{
        var pid=request.parameter.pid;
        var nome=request.parameter.nome;
        var desc=request.parameter.desc;
        var marca=request.parameter.marca;
        var tipo=request.parameter.tipo;
        var preco=request.parameter.preco;
        var ativado=request.parameter.ativado;
        var rowData=[pid, nome, desc, marca, tipo, preco, ativado];
          for(var i=1; i < data.length; i++){
            if(data[i][0] == pid){
              updateIndex=i;
              break;
            }
          }
         sheet.getRange(updateIndex+1,1,1,rowData.length).setValues([rowData]);
  } catch(e){
    console.log(e);
  }
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType);
}

Sheet.getRange(start row,start col,number of rows,number of columns)

Upvotes: 1

Tanaike
Tanaike

Reputation: 201338

  • When pid is included in the values of the column "A", you want to replace the row with rowData.
  • When pid is NOT included in the values of the column "A", you want to append new row with rowData.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modified script:

When your script is modified, it becomes as follows. 

function doGet(request) {
  var sheet = SpreadsheetApp.openById("DOCUMENT_ID");
  var range = sheet.getActiveSheet().getDataRange();  // Added
  var data = range.getValues();  // Modified
  var updateIndex = 0;  // Modified
  try{
    var pid = request.parameter.pid;
    var nome = request.parameter.nome;
    var desc = request.parameter.desc;
    var marca = request.parameter.marca;
    var tipo = request.parameter.tipo;
    var preco = request.parameter.preco;
    var ativado = request.parameter.ativado;
    var rowData = [pid, nome, desc, marca, tipo, preco, ativado];
    for(var i = 1; i < data.length; i++){
      if(data[i][0] == pid){
        data[i] = rowData;  // Added
        updateIndex = i;
      }
    }
    if (updateIndex != 0) {  // Added
      range.setValues(data);
    } else {
      sheet.appendRow(rowData);
    }
  } catch(e){
    console.log(e);
  }
  // In your script, "result" is not declared. Please be careful this.
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType);
}

Note:

  • In your script, result is not declared. Please be careful this.
  • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to Web Apps. So please be careful this.

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions