HtmlFormApp

<a name="top"></a>

<a name="overview"></a>

Overview

This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.

<a name="description"></a>

Description

There is Google Form in the Google service. Google Form can parse the submitted data and put it in the Spreadsheet. But when we want to use the custom form, it is required to use the HTML form on Web Apps, dialog, and sidebar. In this case, it is required to prepare Javascript and Google Apps Script for parsing the form object from the HTML form and appending the parsed values to Spreadsheet. Recently, a bug of the built-in parser from the Javascript side to the Google Apps Script side for parsing the form object from the HTML form had been removed. Ref But, in the current stage, this bug is removed for only Web Apps. Unfortunately, for the dialog and sidebar, this bug has never been removed. And also, unfortunately, the built-in parser from the Javascript side to the Google Apps Script side cannot be used for the multiple files of the input tag. And, this cannot be used except for google.script.run. For example, when the HTML form including the files is submitted using "action" of the form tag, the file content is not included. And then, when the form object is retrieved, it is required to parse the object and put it in the Spreadsheet. From these situations, I thought that when this process can be run using the libraries, that might be useful for users. So I created this.

This Google Apps Script library uses a Javascript library of HtmlFormObjectParserForGoogleAppsScript_js. The form object from the HTML form is parsed using the Javascript library and sent to Google Apps Script side, and the object from Javascript is parsed and put to Spreadsheet using this Google Apps Script library.

medium.com

Easily Implementing HTML Form with Google Spreadsheet as Database using Google Apps Script

Library's project key

1uLJrVXGaI-ceHFl_VC1U5jcynKpR2qnNG2tNPd03QJZw1jCcKw2_Oiwh

Methods

| Methods | Description | | :---------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | appendFormData(object, row) | Parsing the form object parsed by HtmlFormObjectParserForGoogleAppsScript_js and append the value to Spreadsheet. |

About scopes

This library use the scopes of https://www.googleapis.com/auth/drive and https://www.googleapis.com/auth/spreadsheets.

<a name="usage"></a>

Usage:

1. Install library

In order to use this library, please install this library as follows.

1. Create a GAS project.

   • You can use this library for the GAS project of both the standalone type and the container-bound script type.

2. Install this library.

   • Library's project key is 1uLJrVXGaI-ceHFl_VC1U5jcynKpR2qnNG2tNPd03QJZw1jCcKw2_Oiwh.

Methods

<a name="appendFormData"></a>

appendFormData

This method parses the form object parsed by HtmlFormObjectParserForGoogleAppsScript_js and appends the value to Spreadsheet.

// These are all options.
const obj = {
   formData: formData,
   spreadsheetId: "###",
   sheetName: "###",
   sheetId: "###",
   folderId: "###",
   headerConversion: {"header value of Spreadsheet": "name of HTML input tag",,,},
   ignoreHeader: true,
   choiceFormat: true,
   delimiterOfMultipleAnswers: "\n",
   valueAsRaw: true
};
const res = HtmlFormApp.appendFormData(obj);
console.log(res)

Input object

• formData: Form object from HtmlFormObjectParserForGoogleAppsScript_js

• spreadsheetId: Spreadsheet ID you want to put the values.

  • When spreadsheetId is not used, the values are put to the 1st sheet of the created new Spreadsheet.

• sheetName: Sheet name you want to put the values. Default is 1st sheet. When you don't use this, the value is put on the 1st sheet.

• sheetId: Sheet ID you want to put the values. Default is 1st sheet. When you don't use this, the value is put on the 1st sheet.

• folderId: Folder ID of the folder putting the submitted file. Default is "root". When you don't use this, the submitted files are put to the root folder.

• headerConversion: When the header values in the sheet are different from each name of the HTML form, you can put the values to the sheet using this object.

  • For example, when the names of input tags in HTML form are "sample1", "sample2", "sample3", and when the header row is "test1", "test2", "test3", you can set headerConversion as headerConversion: {"test1": "sample1", "test2": "sample2", "test3": "sample3"}.

• ignoreHeader: When this value is true, the submitted values are put to the sheet without using the header row. Default is false.

• choiceFormat: When the HTML form includes the multiple-choice like the checkboxes and radio button, when this value is true, values both true and false are put to the sheet like value1(checked)\nvalue2(unchecked). Default is false. When this value is false, only values when it is true are put on the sheet.

• delimiterOfMultipleAnswers: When the multiple answers are included in an input tag, the values are put to the sheet using this delimiter. Default is ",".

• valueAsRaw: When this value is true, the raw values retrieved from HTML form are used. Default is false. When this value is not used to false, for example, the date is converted to the date object.

• About 2nd argument of appendFormData(object, row), this is from this suggestion. When row is used, the value is put into the specific row of the Spreadsheet. In this case, please set the value of row more than 1.

  • In this case, the submitted row can be forcefully put into the specific row of Google Spreadsheet. So, when you run appendFormData(object, row) by the constant value of row, the submitted row is put into the same row. Please be careful about this.

Output object

This method of appendFormData returns the following object.

{
  "spreadsheet": "Class Spreadsheet",
  "sheet": "Class Sheet",
  "range": "Class Range",
  "values": "values put to the sheet"
}

By using this, you can retrieve the range of the submitted row like res.range.getRow().

Simple sample script

For example, when you want to use this library as a simple mode, you can use the following script.

const obj = {
  formData: formData,
  spreadsheetId: "###",
};
const res = HtmlFormApp.appendFormData(obj);
console.log(res);

In this script, the form data is append to the 1st sheet of "spreadsheetId".

Sample scripts

In the current stage, in order to submit the values using an HTML form, it is considered the following situations. Here, I would like to introduce the sample scripts for each situation using this library.

"Multiple files", "Single file" and "No files" indicate whether the multiple files (using multiple in the input tag.) can be uploaded using the HTML form. Even when "Single file" is indicated, when you put the multiple input tags (type="file") without using multiple, you can upload the multiple files.

Of course, the values from the inputted texts, checkboxes, radio buttons, dropdown lists can be retrieved for all situations.

| Situations | Multiple files | Single file | Sample scripts | | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------- | :---------- | :------------------ | | Using HtmlFormObjectParserForGoogleAppsScript_js, the form object is sent from HTML side to Google Apps Script side by google.script.run on the dialog, sidebar and Web Apps. | Yes | Yes | Sample1 | | Using HtmlFormObjectParserForGoogleAppsScript_js, the form object is sent from HTML side to Google Apps Script by fetch of Javascript with the POST method on the dialog, sidebar, Web Apps and HTML in other site. | Yes | Yes | Sample2 | | Using HtmlFormObjectParserForGoogleAppsScript_js, the form object is sent from HTML side to Google Apps Script by fetch of Javascript with the GET method on the dialog, sidebar, Web Apps and HTML in other site. | | | [Sample3](#sample