CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LSK
LSK

Reputation: 3

Google sheet not accepting data from appendRow or setValues

My sheet suddenly stopped displaying data from the associated script after I changed its location on Google drive. I tried moving it back but still won't work. Ditched appendRow() for insertRow() and setValues() but although row is inserted, no text is showing.

function submitRequest(PettyCashRequest){
  var ss = SpreadsheetApp.openById(dbID);
  var ws = ss.getSheetByName("Register");
  var val = "Something went wrong, record not added!";
  var headingsRow = 1
  //do data validation here

  //put data in a variable
  var data = [PettyCashRequest.description, PettyCashRequest.costcode, PettyCashRequest.ponumber, PettyCashRequest.requestamount, PettyCashRequest.receipt, PettyCashRequest.firstname,
      PettyCashRequest.lastname, PettyCashRequest.idprovided, PettyCashRequest.idreference, PettyCashRequest.approver, new Date()];
  //insert row at first line of table data
  ws.insertRows(2, 1);
  //set values
  ws.getRange(2, 1, data.length, data[0].length).setValues(data);

  /* This code worked just fine for a while
  //add row to bottom of sheet
  ws.appendRow([PettyCashRequest.description, PettyCashRequest.costcode, PettyCashRequest.ponumber, PettyCashRequest.requestamount, PettyCashRequest.receipt, PettyCashRequest.firstname,
      PettyCashRequest.lastname, PettyCashRequest.idprovided, PettyCashRequest.idreference, PettyCashRequest.approver, new Date()]);
  */

Upvotes: 0

Views: 407

Answers (1)

Cooper
Cooper

Reputation: 64032

Try it this way:

function submitRequest(PettyCashRequest){
  var ss = SpreadsheetApp.openById(dbID);
  var ws = ss.getSheetByName("Register");
  var val = "Something went wrong, record not added!";
  var headingsRow = 1;
  var data = [[PettyCashRequest.description, PettyCashRequest.costcode, PettyCashRequest.ponumber, PettyCashRequest.requestamount, PettyCashRequest.receipt, PettyCashRequest.firstname,
      PettyCashRequest.lastname, PettyCashRequest.idprovided, PettyCashRequest.idreference, PettyCashRequest.approver, new Date()]];
  ws.insertRows(2, 1);
  ws.getRange(2,1,data.length,data[0].length).setValues(data);

data has to be a 2 dimensional array for setValues()

Upvotes: 1

Related Questions