tanaike - Google Apps Script, Gemini API, and Developer Tips

The Thinker

Creating Quizzes in Google Form using Google Forms Service with Google Apps Script

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

  • This sample script doesn’t include the error procession. So please add it for your actual situation.

Creating Quizzes in Google Form using Google Forms API with Google Apps Script

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).

Retrieving Summary of Google Document using Google Apps Script

Gists

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

Merging Columns with Same Header Title in Google Spreadsheet using Google Apps Script

Gists

This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each column are merged using the same header title.

In this sample script, the sample input and output situations are as follows.

Sample situation

Input: “Sheet1”

Output: “Sheet2”

Sample script

function myFunction() {
  const srcSheetName = "Sheet1"; // This sheet is "Input" situation.
  const dstSheetName = "Sheet2"; // This sheet is "Output" situation.

  const transpose = (ar) => ar[0].map((_, c) => ar.map((r) => r[c]));
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const values = src.getDataRange().getValues();
  const temp = [
    ...transpose(values)
      .reduce(
        (m, [a, ...b]) => m.set(a, m.has(a) ? [...m.get(a), ...b] : [a, ...b]),
        new Map()
      )
      .values(),
  ];
  const res = transpose(temp);
  dst.getRange(1, 1, res.length, res[0].length).setValues(res);
}
  • In this sample script, the following flow is used.
    1. Retrieve values from “Sheet1”.
    2. Tanspose the retrieved values.
    3. Create an array using Map object.
    4. Tanspose the created array.
    5. Put the array to “Sheet2”.

Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

Gists

Introduction

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. The destructuring assignment can be used without V8 runtime. But, the spread syntax is required to be used with V8 runtime. Recently, I often saw the script using them at Stackoverflow. And also, I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.

Bug of Create Method of Google Forms API was Removed

Gists

When I tested Google Forms API, I noticed that when a new Google Form is created by the method of forms.create, there is not title of the created Google Form. So, I have reported this to Google issue tracker. Ref Today, I confirmed that this bug has been removed.

And, when I saw the official document, I noticed that the following document has been added. Ref

In the current stage, when the following curl command is used, a new Google Form with the file title of sampleFormTitle and the form title of sampletitle can be created.