How do I add information to a row with already containing some cells?

Question

I am using appendRow() to insert values in Google Sheet, and even if some fields are empty, it adds the values to the next line if any cell contains content.

Example: Cell C1 has a Sum formula, =SUM(A1,B1) When I insert a row with appendRow(), the values are added to A2 and B2 instead of A1 and B1, due to the content of C1.

Is there any way to solve this?

function showUserForm() {

var template = HtmlService.createTemplateFromFile("userForm");

var html = template.evaluate();

html.setTitle("Form");

SpreadsheetApp.getUi().showSidebar(html);

}

function appendData(data){

var ws= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records");
  ws.appendRow([data.depo,data.phone,data.game]);

}

function errorMsg(){

Browser.msgBox("Name & Phone Required");

}

Cooper · Answer

Try this:

function appendData(data){
  var sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records");
  var rg=sh.getRange(sh.getLastRow(),1);
  rg.setValue(data.depo);//column A
  rg.offset(0,1).setValue(data.phone);//column B
  rg.offset(0,3).setValue(data.game);//column D
}

or

function appendData(data){
  var sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records");
  var rg=sh.getRange(sh.getLastRow(),1,1,4);
  var vA=rg.getValues();
  vA[0][0]=data.depo;
  vA[0][1]=data.phone;
  vA[0][3]=data.game;
  rg.setValues(vA);
}

How do I add information to a row with already containing some cells?

One Answer

Add your own answers!

Ask a Question