Passing variables into .setValues() empties cells

Question

When inputting data from a form and passing it to a Google Apps Script function using .setValues() to update information on a given row, each cell returns empty and all data inside those cells are removed

I am working on creating a database for customers. I am taking variables from an input modal and passing them on to a Google Apps Script function. I can get it to function properly when I provide strings directly, however when filling out the form and submitting, the entire cell range returns empty. If there is data in the cells, it is deleted.

When I switch to 5 rows and 1 column for the range, it will update with the proper data inputted. So I feel like it might have something to do with the array I am passing into the Apps Script function.

Anyone have an idea as to why the data won't populate the cells when passing through as 1 row and 5 columns while passing through as 1 column and 5 rows will return the correct data?

//Client-side function that passes variables to Apps Script function

function updateContact() {
  let name = document.getElementById("name").value;
  let address = document.getElementById("address").value;
  let phone = document.getElementById("phone").value;
  let email = document.getElementById("email").value;
  let notes = document.getElementById("notes").value;
  google.script.run.updateContact(name, address, phone, email, notes);
}

//Google Apps Script function to Replace Values in Range

function updateContact(name, address, phone, email, notes) {
  var ss = SpreadsheetApp.openByUrl(
    "https://docs.google.com/spreadsheets/d/1ek3XVSyoxmS-ef5dgph8KKpe4fmnyHSWxLl2-uH5xyg/edit#gid=1696663263"
  );
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(2, 1, 1, 5);
  range.setValues([[name, address, phone, email, notes]]);
}

score 1 · Answer 1 · answered Sep 27 '19 at 21:55

This works for me:

function updateContact(one,two,three) {
  var one=one||1;
  var two=two||2;
  var three=three||3;
  var ss = SpreadsheetApp.openById('ssid');
  var sheet = ss.getSheetByName('Sheet1');
  var range = sheet.getRange(2, 1, 1, 3);
  range.setValues([[one, two, three]]);
}

Passing variables into .setValues() empties cells

1 Answers1