How to read which checkboxes have been selected (HTML form to Google Sheet)

Question

I'm using Praveen Kumar's script to send HTML form data to a Google Sheet (https://www.youtube.com/watch?v=39XZBKmcMfE&ab_channel=CodingMSTR).

It works great, but the only example code was for simple text inputs. How might this work for a checkbox input?

The form:

Currently if any of the checkboxes are selected, the value "on" is passed, otherwise blank but I need to see in the Google Sheet which of the checkboxes have been selected:

The HTML form:


    
    
    
    
    
        
            How can I contact you?
            Text
            Phone - daytime
            Phone - early evening

The Javascript in the HTML page:

The Appscript doGet function:

  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, 1, 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(nextRow, 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()
  }
}

Any help much appreciated.

UPDATE: After Tanaike's suggestion, the sheet receives a value but when selecting more than one item, only the first one is sent to the sheet:

The updated form:


     How can I contact you?
     Text
     Phone - daytime
     Phone - early evening

The updated Appscript:

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

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

    var newRow = headers.map(function (header) {
    return header === 'timestamp' ? new Date() : e.parameters[header].join(",");
    });

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

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

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

Tanaike · Answer

When I saw your HTML, it seems that value is not set in the input tag. So, as a simple modification, how about the following modification?

HTML side:

Please modify your HTML as follows.

From:

Text
Phone - daytime
Phone - early evening

To:

Text
Phone - daytime
Phone - early evening

Google Apps Script side:

In order to use the multiple responses, please modify Google Apps Script as follows.

From:

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

To:

var newRow = headers.map(function (header) {
  return header === 'timestamp' ? new Date() : e.parameters[header].join(",");
});

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

Added:

If your HTML and Javascript are put in the same Google Apps Script project of doPost, I thought that the following modification might be able to be used.

HTML & Javascript: `index.html`


    
    
    
    
    
        
            How can I contact you?
Text
Phone - daytime
Phone - early evening

Google Apps Script: `Code.gs`

// If you are using a dialog and sidebar, please use it. If you are using Web Apps, you can use this.
function doGet() {
  return HtmlService.createHtmlOutputFromFile("index"); // Please set the html filename.
}

function saveData(e) {
  var sheetName = "###"; // Please set sheet name.
  var scriptProp = PropertiesService.getScriptProperties();

  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, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1
    var newRow = headers.map(function (header) {
      return header === 'timestamp' ? new Date() : Array.isArray(e[header]) ? e[header].join(",") : e[header];
    })
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    return { 'result': 'success', 'row': nextRow };
  } catch (e) {
    return { 'result': 'error', 'error': e };
  } finally {
    lock.releaseLock()
  }
}

In this modification, even when the Web Apps is deployed as a new version and the URL of Web Apps is changed, this script works because the Web Apps URL is not used for fetch API.
When you use this script, please copy and paste the above scripts to a Google Apps Script project, and please deploy Web Apps. When you access the created Web Apps URL, the HTML is opened. And, when you submit the HTML form, the values are put on the Spreadsheet.

user30hu · Answer

Here's an example that create checkboxes on the fly and also a couple static ones and then reads them. It's actually a complete solution for backing up your script functions into ascii files.

HTML:

GS:

function saveScriptBackupsDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('backupscripts1'), 'Script Files Backup Dialog');
}

function scriptFilesBackup(obj) {
  console.log(JSON.stringify(obj));
  const scriptId = obj.script.trim();
  const folderId = obj.folder.trim();
  const saveJson = obj.saveJson;
  const saveFiles = obj.saveFiles;
  const fA = obj.selected;
 
  if (scriptId && folderId) {
    const base = "https://script.googleapis.com/v1/projects/"
    const url1 = base + scriptId + "/content";
    const url2 = base + scriptId;
    const options = { "method": "get", "muteHttpExceptions": true, "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    const res1 = UrlFetchApp.fetch(url1, options);
    const data1 = JSON.parse(res1.getContentText());
    const files = data1.files;
    const folder = DriveApp.getFolderById(folderId);

    const res2 = UrlFetchApp.fetch(url2, options);
    const data2 = JSON.parse(res2.getContentText());
    let dts = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd-HH:mm:ss");
    let subFolderName = Utilities.formatString('%s-%s', data2.title, dts);
    let subFolder = folder.createFolder(subFolderName);
    if (saveFiles) {
      files.forEach(file => {
        if (file.source && file.name) {
          let ext = (file.type == "HTML") ? ".html" : ".gs";
          if (~fA.indexOf(file.name)) {
            subFolder.createFile(file.name + ext, file.source, MimeType.PLAIN_TEXT)
          }
        }
      });
    }
    if (saveJson) {
      subFolder.createFile(subFolderName + '_JSON', res1, MimeType.PLAIN_TEXT)
    }
  }
  return { "message": "Process Complete" };
}

And here's what the Dialog Looks like I use this code to save my functions in ascii files.

How to read which checkboxes have been selected (HTML form to Google Sheet)

2 Answers

HTML side:

From:

To:

Google Apps Script side:

From:

To:

Note:

Added:

HTML & Javascript: index.html

Google Apps Script: Code.gs

HTML & Javascript: `index.html`

Google Apps Script: `Code.gs`