I am trying to update a Google Sheet using a modal dialog with three fields in it. The cells to update are relative to the location of the active cell. The first field (session) goes in the active cell. The second field (pTrainer) goes in the cell directly below the active cell. The third field (sTrainer) goes in the cell to the right of the pTrainer cell.
My Code.gs file is as follows.
```
//@OnlyCurrentDoc
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu("Add Session")
.addItem("Add Session", "showAddSessionDialog")
.addToUi();
}
function showAddSessionDialog() {
var widget =
HtmlService.createHtmlOutputFromFile("sessionForm.html");
SpreadsheetApp.getUi().showModalDialog(widget, "Add Session");
}
function insertSessionFromFormSubmit(form) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var activeCell = sheet.getActiveCell();
var sRow = activeCell.getRow();
var sCol = activeCell.getColumn();
var sessRange = sheet.getRange(sRow,sCol);
var priRange = sheet.getRange(sRow + 1,sCol + 0);
var secRange = sheet.getRange(sRow + 1,sCol + 1);
var sessCell = form.session;
var priCell = form.pTrainer;
var secCell = form.sTrainer;
sessRange.setValues(sessCell);
priRange.setValues(priCell);
secRange.setValues(secCell);
}
```
My html file (sessionForm.html) is as follows.
```
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function submitForm() { google.script.run.insertSessionFromFormSubmit(document.getElementById("sessionForm"));
}
</script>
</head>
<body>
<div>
<div id="form">
<form id="sessionForm">
<label for="session">Enter session:</label>
<input type="text" id="session" name="session"><br><br>
<label for="trainer">Enter primary trainer:</label>
<input type="text" id="pTrainer" name="pTrainer"><br><br>
<label for="trainer">Enter secondary trainer:</label>
<input type="text" id="sTrainer" name="sTrainer"><br><br>
<div>
<input type="button" value="Submit" onclick="submitForm();">
</div>
</form>
</div>
</div>
</body>
</html>
```
The code does not raise any errors--neither does it pass the values that I enter into the dialog into the sheet. Any advice would be most appreciated.
The reason it's not working is because sessCell, priCell, and secCell are one-dimensional arrays of values, whereas you're using setValues(values), which is A two-dimensional array of values.
Change:
sessRange.setValues(sessCell);
priRange.setValues(priCell);
secRange.setValues(secCell);
To:
sessRange.setValue(sessCell);
priRange.setValue(priCell);
secRange.setValue(secCell);