You can see the detail information here https://github.com/tanaikech/HtmlFormApp
Gists
Abstract
This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.
-
v1.0.1 (May 29, 2023)
- When multiple files are uploaded, each URL was set as the hyperlink.
You can see the detail information here https://github.com/tanaikech/HtmlFormApp
Gists
This is a sample script for opening and closing Google Forms on time using Google Apps Script.
In order to test this sample script, please do the following flow.
Usage
Please create a new Google Form and set your sample questions. And, please open the script editor of Google Form.
2. Prepare sample script.
Please copy and paste the following script to the script editor of Google Form. And, please set the values of start and end times you want.
Gists
This is a report related to the rule of item IDs for questions of Google Forms.
When the questions are created using the method of batchUpdate with Google Forms API, the created questions have the item IDs when the item IDs are not given in the request body. ( https://developers.google.com/forms/api/reference/rest/v1/forms#item ) For example, when you want to create a question and update the created question in one API call, it is required to include the custom item ID in the request body. But, it seems that in the current stage, the information of the item ID has never been published. So, in this report, I would like to consider the rule of item IDs of questions for Google Forms API.
Gists
This is a sample script for analyzing the responses from Grid Items of Google Form using Google Apps Script.
In this sample situation, all responses are retrieved from Grid Items of Google Form, and the average values of each row of each question are calculated. And, the result is put on the Spreadsheet.
Sample situation
The sample Google Form is as follows.
Output: Sample Spreadsheet
The sample output is as follows.
Gists
This is a sample script for creating quizzes in Google Form using Google Forms Service with Google Apps Script.
Usage
1. Prepare questions and answers.
In this sample, the questions and answers are prepared using Spreadsheet as follows.
2. Sample script.
This script is container-bound script of the above Spreadsheet.
function myFunction() {
const formTitle = "sample"; // This is a form title.
const sheetName = "Sheet1"; // This is a sheet name.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const [, ...values] = sheet
.getDataRange()
.getDisplayValues()
.filter((r) => r.join("") != "");
const obj = values.map(([a, b, c]) => {
const answers = b
.split("\n")
.map((e) => e.trim())
.filter(String);
const correct = c
.split("\n")
.map((e) => e.trim())
.filter(String);
return {
question: a,
answers,
correct,
point: 1,
type: correct.length == 1 ? "addMultipleChoiceItem" : "addCheckboxItem",
};
});
const form = FormApp.create(formTitle)
.setIsQuiz(true)
.setTitle("Sample questions");
obj.forEach(({ question, answers, correct, point, type }) => {
const choice = form[type]();
const choices = answers.map((e) =>
choice.createChoice(e, correct.includes(e) ? true : false)
);
choice.setTitle(question).setPoints(point).setChoices(choices);
});
}
- When this script is run using the prepared Spreadsheet, you can see the result Google Form at the top image in this post.
Note
Gists
This is a sample script for creating quizzes in Google Form using Google Forms API with Google Apps Script. Recently, Google Forms API has been officially published, and it got to be able to be used by users. By this, quizzes in Google Form can be created using Google Forms API.
Here, there is one thing that can be achieved by Google Forms API. When Google Forms API is used, each choice in each question can be shuffled. This cannot be achieved with Google Forms Service (FormApp).
Finally, Google Forms API has been officially released as version 1.
Create surveys, quizzes, and more using the Google Forms API, now generally available
The Google Forms API provides programmatic access for managing Google Forms and acting on responses— empowering developers to build powerful integrations on top of Forms.
Forms API
Overview
This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
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 is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.
HtmlFormObjectParserForGoogleAppsScript_js was updated to v1.0.1.
You can see the detail of this at https://github.com/tanaikech/HtmlFormObjectParserForGoogleAppsScript_js
Google is proud to announce the Google Forms API! The Forms API is currently available in Restricted Beta, with Open Beta expected to follow in Q4.
Forms API
Gists
This is a sample script for putting all response values from Google Form to Google Spreadsheet using Google Apps Script.
Sample script
Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of formId and sheetName.
function myFunction() {
const formId = "###"; // Please set the Google Form ID.
const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.
// Retrieve all response values from Google Form.
const form = FormApp.openById(formId);
const headers = ["date", ...form.getItems().map(e => e.getTitle())];
const values = [headers, ...form.getResponses().map((f) => {
const timeStamp = f.getTimestamp();
return f.getItemResponses().reduce((o, i) => {
const r = i.getResponse();
return Object.assign(o, {
[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r,
});
}, { date: timeStamp });
}).map((o) => headers.map((t) => o[t] || ""))];
// Put the values to Spreadsheet.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(1, 1, values.length, values[0].length).setValues(values);
}
- When this script is run, all response values are retrieved from Google Form and put them to the Spreadsheet.
Note
- At Google Form, when the empty answer is submitted, the question has no value. By this, it is required to consider this. So at first, the titles are retrieved from the items, and the values are created using the item titles. I thought that this might be an important point.
References
- This sample script was used for the following threads.
Gists
This is a sample script for directly submitting answers to Google Form using Google Apps Script.
The sample Google Form is as follows.
For this Google Form, this sample script submits the values of sample text, option2 and option1, option2, sample option to Google Form.
Sample script
For the multiple answers, it seems that it is required to send the values as the query parameter. I thought that the same key is used.
Gists
-
Published: September 15, 2021
-
Updated: September 17, 2021
- From the discussions, added data by changing the wait time of LockService for Web Apps.
Kanshi Tanaike
Introduction
When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such a situation was investigated.
Overview
This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.
Description
HTML form object is parsed by this library, and the object can be sent to Google Apps Script using google.script.run. After the V8 runtime got to be able to be used for Google Apps Script, when the file input tag is included in the HTML form object, the object sent to Google Apps Script using google.script.run cannot be directly used. Because it seems that the binary file data cannot be parsed when it is sent with google.script.run. Ref In the current stage, as the workaround, the file data is required to be sent to Google Apps Script using google.script.run after it was converted to the byte array and the base64 data. I think that this might be resolved in the future update. But I thought that as the current workaround, when the parser for converting this is prepared, it might be useful for users, because I saw the several questions which have this issue at Stackoverflow.
Gists
This is a sample script for using the values submitted from the HTML form using Google Apps Script and Javascript. In this case, the values include the files.
Issue
<form>
Text: <input type="text" name="sampleText1" /><br />
Single file: <input type="file" name="sampleFile1" /><br />
<input
type="submit"
name="button"
value="submit"
onclick="main(this.parentNode)"
/>
</form>
<script>
function main(e) {
google.script.run.sample(e);
}
</script>
This is a simple sample script for sending the values of form to Google Apps Script. In this case, texts and file are sent. When the button is clicked, main() is run. In this case, this.parentNode is sent to google.script.run.sample(this.parentNode). At that time, at Google Apps Script side, the text value can be correctly retrieved. But the file cannot be correctly sent. When the file is created as a file on Google Drive, the file is broken. I think that the reason of this issue might be due to character code.