Checkboxes in Google Sheets w/ Apps Script

Question

I have a simple script in a Google Sheet to collect responses from a Contact page on a website I'm building. I wanted to put checkboxes in the first column so you could mark which messages have been dealt with.

My problem is, whenever I put the checkboxes in, the responses will always come in one row below where the checkboxes end (if they end at row 10, the responses will start at row 11, etc). I don't believe there's anything wrong with the script as it works fine without the checkboxes...any thoughts as to why this would happen?

EDIT: I'm assuming it has to do with this line:

var nextRow = sheet.getLastRow() + 1

The LastRow would always be the last checkbox. How could I modify it to work?

var sheetName = 'Contact'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 2, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(2, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

The Sheet.getLastRow() is determine by the last row that has any data in it. Checkboxes contain either "TRUE" or "FALSE". Array functions often cause a similar problem. You may what to just calculate a column height. — Cooper
Add a checkbox through the script, when a new entry is added, instead of manually doing it. — TheMaster

roma roma · Accepted Answer · 2020-05-23T17:50:17

As TheMaster stated, it'd be better to add checkboxes on demand, while inserting new row. Assuming checked would be state and insertRow is where you plan to add data code can be like this

var checked = true;
var insertRow = 2;
SpreadsheetApp.getActive().getActiveSheet().getRange(insertRow, 1).insertCheckboxes().setValue(checked);

Checkboxes in Google Sheets w/ Apps Script

1 Answers