ロマンの紅茶の由無し事 – いろいろやってみたいよね

Googleフォームを自動生成②

前回書いた記事の更新版です。

グリッド式の選択肢が作れるようになりました。

※この記事の内容は、Google Apps Script（GAS)を扱える知識が必要です。

こちらを開いてコピーを作成すると早いです。

一応、一から作る手順は以下の通りです。

手順１　スプレッドシートを作成する

フォームのタイトルや説明、質問事項を記入したスプレッドシートを作成します。

スクリプトの都合上、質問等の選択肢は最大10個までにしています。編集すれば増やせます。

手順2　シートにApp Scriptを記述する

以下のコードをすべて貼り付けます。
（シートをコピーした場合は、既に入っています。）

function createForm() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート
  const ssId = ss.getId(); // スプレッドシートIDを取得
  const parentFolder = DriveApp.getFileById(ssId).getParents(); // IDからスプレッドシートのファイルを取得⇒親フォルダを取得
  const folderId = parentFolder.next().getId(); // 親フォルダIDを取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // フォームのタイトルをB1から取得
  const formTitle = sheet.getRange('B1').getDisplayValue();
  // フォームの説明をB2から取得
  const formDescription = sheet.getRange('B2').getDisplayValue();

  const firstRow = 5;
  const lastRow = sheet.getLastRow();
  const dataRows = lastRow - (firstRow - 1);

  //質問の選択肢は、N列(14)までの最大10個
  var questionList = sheet.getRange(firstRow, 1, dataRows, 14).getDisplayValues();

  questionList = questionList.map(question => {
    return {
      title: question[0],       //質問のタイトル
      helpText: question[1],    //質問の説明
      type: question[2],        //回答の種類
      must: question[3],        //必須かどうか
      choices: [question[4], question[5], question[6], question[7], question[8],question[9],question[10],question[11],question[12],question[13]]
    }
  });

  //フォームのタイトルを設定
  const form = FormApp.create(formTitle);
  //フォームの説明を設定
  form.setDescription(formDescription);

  //グリッド用データ保存場所
  let gridRows = [];
  let gridColumns = [];
  let gridContent = gridColumns.length;
  let gridQ = 0 ;
  let gTitle = '';
  let gHelpText = '';
  let gTrue = '';

  
  //以下、質問の種類による分岐
  questionList.forEach(question => {

    //グリッド要素
    if (question.type == '選択式(グリッド)') {
      console.log('選択式(グリッド)');
      gridQ = 1;
      gTitle = question.title;
      gHelpText = question.helpText;
      gTrue = question.must;
      question.choices.forEach((choice) => {
        if (choice != '') {
          gridColumns.push(choice);
        };
      gridContent = gridColumns.length;
      });
    }
    else if (question.type == 'チェックボックス(グリッド)') {
      gridQ = 2;
      gTitle = question.title;
      gHelpText = question.helpText;
      gTrue = question.must;
      question.choices.forEach((choice) => {
        if (choice != '') {
          gridColumns.push(choice);
        };
      gridContent = gridColumns.length;
      });
    }
    else if (gridContent > 0){    //どちらかのグリッド形式だったら
      if (gridQ == 1 ){
        var choiceItem = form.addGridItem();
      } else if (gridQ == 2){
        var choiceItem = form.addCheckboxGridItem();
      }

      question.choices.forEach((choice) => {
        if (choice != '') {
          gridRows.push(choice);
        }
      });
      choiceItem.setTitle(gTitle)
        .setHelpText(gHelpText)
        .setRows(gridRows)
        .setColumns(gridColumns);
        if (gTrue =='必須'){
        choiceItem.setRequired(true)
        }
      //グリッド要素を初期化
      gridRows = [];
      gridColumns = [];
      gridQ = 0;
      gridContent = gridRows.length;
    }
    else if (question.type == '記述式'){
      console.log('記述式')
      var choiceItem = form.addTextItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == '段落'){
      console.log('段落')
      var choiceItem = form.addParagraphTextItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == 'ラジオボタン') {
      console.log('ラジオボタン')
      var choiceItem = form.addMultipleChoiceItem();
      var choiceList = [];
      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == 'チェックボックス') {
      console.log('チェックボックス')
      var choiceItem = form.addCheckboxItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == 'プルダウン') {
      console.log('プルダウン')
      var choiceItem = form.addListItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == '均等目盛') {
      console.log('均等目盛')
      var choiceItem = form.addScaleItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          choiceList.push(choice);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setBounds(choiceList[0],choiceList[1])
        .setLabels(choiceList[2],choiceList[3]);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == '日付'){
      console.log('日付')
      var choiceItem = form.addDateItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == '時刻'){
      console.log('時刻')
      var choiceItem = form.addTimeItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
      else if (question.type == '日時'){
      console.log('日時')
      var choiceItem = form.addDateTimeItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else{
      Browser.msgBox('「'+ question.title +'」は作成できない問題形式です');
    }
  });

  //作成したフォームのアドレスをB3セルに入力
  sheet.getRange('B3').setValue(form.getEditUrl());
  Browser.msgBox('「' + formTitle + '」の作成が完了しました');

  //作成したフォームをスプレッドシートのフォルダに移動
  const formFile = DriveApp.getFileById(form.getId())
  DriveApp.getFolderById(folderId).addFile(formFile)

  // 作成したフォームを開く
  var url = form.getPublishedUrl();
  var script = "<script>window.open('" + url + "', '_blank').focus()</script>";
  var html = HtmlService.createHtmlOutput(script);
  SpreadsheetApp.getUi().showModalDialog(html, '作成したフォームを開きます　' );

  // 通常のURLをログに表示
  console.log('URL=  ' +　url);
  // 短縮したURLをログに表示
  console.log('短縮URL=  ' + form.shortenFormUrl(url));
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

function createForm() {

const ss = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート

const ssId = ss.getId(); // スプレッドシートIDを取得

const parentFolder = DriveApp.getFileById(ssId).getParents(); // IDからスプレッドシートのファイルを取得⇒親フォルダを取得

const folderId = parentFolder.next().getId(); // 親フォルダIDを取得

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// フォームのタイトルをB1から取得

const formTitle = sheet.getRange('B1').getDisplayValue();

// フォームの説明をB2から取得

const formDescription = sheet.getRange('B2').getDisplayValue();

const firstRow = 5;

const lastRow = sheet.getLastRow();

const dataRows = lastRow - (firstRow - 1);

//質問の選択肢は、N列(14)までの最大10個

var questionList = sheet.getRange(firstRow, 1, dataRows, 14).getDisplayValues();

questionList = questionList.map(question => {

return {

title: question[0], //質問のタイトル

helpText: question[1], //質問の説明

type: question[2], //回答の種類

must: question[3], //必須かどうか

choices: [question[4], question[5], question[6], question[7], question[8],question[9],question[10],question[11],question[12],question[13]]

}

});

//フォームのタイトルを設定

const form = FormApp.create(formTitle);

//フォームの説明を設定

form.setDescription(formDescription);

//グリッド用データ保存場所

let gridRows = [];

let gridColumns = [];

let gridContent = gridColumns.length;

let gridQ = 0 ;

let gTitle = '';

let gHelpText = '';

let gTrue = '';

//以下、質問の種類による分岐

questionList.forEach(question => {

//グリッド要素

if (question.type == '選択式(グリッド)') {

console.log('選択式(グリッド)');

gridQ = 1;

gTitle = question.title;

gHelpText = question.helpText;

gTrue = question.must;

question.choices.forEach((choice) => {

if (choice != '') {

gridColumns.push(choice);

};

gridContent = gridColumns.length;

});

}

else if (question.type == 'チェックボックス(グリッド)') {

gridQ = 2;

gTitle = question.title;

gHelpText = question.helpText;

gTrue = question.must;

question.choices.forEach((choice) => {

if (choice != '') {

gridColumns.push(choice);

};

gridContent = gridColumns.length;

});

}

else if (gridContent > 0){ //どちらかのグリッド形式だったら

if (gridQ == 1 ){

var choiceItem = form.addGridItem();

} else if (gridQ == 2){

var choiceItem = form.addCheckboxGridItem();

}

question.choices.forEach((choice) => {

if (choice != '') {

gridRows.push(choice);

}

});

choiceItem.setTitle(gTitle)

.setHelpText(gHelpText)

.setRows(gridRows)

.setColumns(gridColumns);

if (gTrue =='必須'){

choiceItem.setRequired(true)

}

//グリッド要素を初期化

gridRows = [];

gridColumns = [];

gridQ = 0;

gridContent = gridRows.length;

}

else if (question.type == '記述式'){

console.log('記述式')

var choiceItem = form.addTextItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '段落'){

console.log('段落')

var choiceItem = form.addParagraphTextItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == 'ラジオボタン') {

console.log('ラジオボタン')

var choiceItem = form.addMultipleChoiceItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == 'チェックボックス') {

console.log('チェックボックス')

var choiceItem = form.addCheckboxItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == 'プルダウン') {

console.log('プルダウン')

var choiceItem = form.addListItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == '均等目盛') {

console.log('均等目盛')

var choiceItem = form.addScaleItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

choiceList.push(choice);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setBounds(choiceList[0],choiceList[1])

.setLabels(choiceList[2],choiceList[3]);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == '日付'){

console.log('日付')

var choiceItem = form.addDateItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '時刻'){

console.log('時刻')

var choiceItem = form.addTimeItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '日時'){

console.log('日時')

var choiceItem = form.addDateTimeItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else{

Browser.msgBox('「'+ question.title +'」は作成できない問題形式です');

}

});

//作成したフォームのアドレスをB3セルに入力

sheet.getRange('B3').setValue(form.getEditUrl());

Browser.msgBox('「' + formTitle + '」の作成が完了しました');

//作成したフォームをスプレッドシートのフォルダに移動

const formFile = DriveApp.getFileById(form.getId())

DriveApp.getFolderById(folderId).addFile(formFile)

// 作成したフォームを開く

var url = form.getPublishedUrl();

var script = "<script>window.open('" + url + "', '_blank').focus()</script>";

var html = HtmlService.createHtmlOutput(script);

SpreadsheetApp.getUi().showModalDialog(html, '作成したフォームを開きます　' );

// 通常のURLをログに表示

console.log('URL= ' +　url);

// 短縮したURLをログに表示

console.log('短縮URL= ' + form.shortenFormUrl(url));

}

サンプルExcelの中にも、コードは記述してあります。

手順3　ボタンにスクリプトを割り当てる

最後に、スプレッドシートのボタンにスクリプトを割り当てれば完成です。

ボタンを押すと、スプレッドシートと同じ場所にフォームが出来ています。

※押したら押した分だけ次々にできるので気を付けましょう…

あとは、集計するスプレッドシートは、フォームから作るのがいいでしょう。自動で作ると、どのファイルか分からなくなりますので…。あとは、やはり作ったら動作確認を忘れずに…

今後の予定

選択肢によって、違うページに飛ぶようにしたい。技術的には可能です。
しかし、飛ぶページを最初に作成し、選択肢を考えておくなど、最初の設計が出来ていないと作れないマクロしか作れていません…。

終わりに…

同じですが、多くの学校でこういったものはどんどん使われるようになっているのだから、本当は個人で作るよりは…、と思ってしまいますね。

コメントを残すコメントをキャンセル

Appsheetでリストの内容を動的に変える③

前書きページはこちら

今回は、リストをテーブルから参照する方法です。

やってみよう

まず、spreadsheetを作ったら、拡張機能→Appsheet→アプリ作成として、テンプレートを使わずに作ります。※1，2は2列法と同じです。

テーブルを作る
入力シートのUXを作る
DataのColumnsの設定をする

1．テーブルを作る

2．入力シートのUXを作る

Primary Viewsに入力シートのUXを追加します。

次に、人を選んだ際の入力画面を作ります。下の方に「入力シート＿Detail」があるので、「Quick edit columns]に２つ追加します。

3.DataのColumnsの設定をする

ここで”入力シート”のリストにしたいcolumnをRefにします…）

ここでは、選んだ料理をRefにします。

Refに設定すると、自動で「Souece table」を選ぶ画面になるので、「元データ」を選択。そして、「選んだ料理」に数式を入力します。

ifs([食べたい食種]="和食",select(元データ[和食],true),[食べたい食種]=”中華”,select(元データ[中華],true),[食べたい食種]=”洋食”,select(元データ[洋食],true))

1	ifs([食べたい食種]="和食",select(元データ[和食],true),[食べたい食種]=”中華”,select(元データ[中華],true),[食べたい食種]=”洋食”,select(元データ[洋食],true))

書いてある通りですが、ifs関数で、[食べたい食種]を選んだ結果によって、select関数で、「元データ」シートの対応するColumnが返るようにしています。

そして、「元データ」のColumnを次のようにします。

大元の「食種」にKEY,LABELともにチェックを入れます。そして、選択肢のTYPEをEnumにします。(しなくても動くけど…)。さらに、Regenerate structureでデータを読み直します。　最後にSAVEを忘れずに。

こんな感じで動きますが、動画にもあるように料理名の横に黄色の△が表示されてしまいます。（スマホで確認すると見えませんでした。）

これは、「元データ」のLABELに設定した列にないものにつくそうです。消し方は分かりません…。誰かご指導ください…。

以上になりますが、食種を追加したときに、それ用に列を追加、数式も修正が必要になります。まだまだ方法はあるはず…

終わりに

この方法だと、新しい項目の追加が結構面倒なのです…。詳細項目は横に見ていくので、食種に対して追加！とならず、全食種を入力するような画面になってしまいます。管理が面倒かも。

コメントを残すコメントをキャンセル

Appsheetでリストの内容を動的に変える②

前書きページはこちら

今回は、2つのリストを2列にすべて書く方法です。

上の一覧から、下の一覧へ入力します。

前準備として、「食べたい食種」にデータ入力規則を設定。

やってみよう！

まず、spreadsheetを作ったら、拡張機能→Appsheet→アプリ作成として、テンプレートを使わずに作ります。

テーブルを作る
入力シートのUXを作る
DataのColumnsの設定をする

1．テーブルを作る

2．入力シートのUXを作る

Primary Viewsに入力シートのUXを追加します。

次に、人を選んだ際の入力画面を作ります。下の方に「入力シート＿Detail」があるので、「Quick edit columns]に２つ追加します。

3.DataのColumnsの設定をする

ここで他のシートのリストを引用するためのポイント？は、”入力シート”のリストにしたいcolumnをRefにすることでした…(これがよく理解できず時間がかかった…）

ここでは、選んだ料理をRefにする、ということです。

Refに設定すると、自動で「Souece table」を選ぶ画面になるので、「元データ」を選択

そして、下の「Valid if」に、リストが連動するように、

select(元データ[詳細],[食種]=[_THISROW].[食べたい食種])

1	select(元データ[詳細],[食種]=[_THISROW].[食べたい食種])

と入力します。次に、元データを見ると、「Related…」が増えています。そして、「詳細」にkEYとLABELのチェックを入れます。

こんな感じで動作します。

終わりに

前準備で入力規則を設定しましたが、しなくてもRefで持ってくることはできます。その際、開発画面上では詳細に注意の△マークが付き、モヤっとします…。

また、新しい項目を追加する際、どんどん縦方向に追加できるので、この方法は楽でした。

コメントを残すコメントをキャンセル

Appsheetでリストの内容を動的に変える①

スプレッドシートが、共同で作業するのに便利だと知りました。そして、それをAppsheetを使えば、簡単にスマホアプリ的なものを作れると知り、にわか知識で取り組みました。

さて、選択肢Aの内容に応じて、選択肢Bの内容を動的に変えたい。よくあることで、Excelやspreadsheetでも勉強しました。（それはまた別な記事に書きます。）

で、Appshetやってみようと検索すれど、いくつか出てくるけど、正直言ってよくわからん…

そんな人の一助になれば嬉しく思います。

2通りの方法を試しました。

選択項目が2つあるとして記事を書きます。元となるデータの書き方次第で、好みの方をどうぞ。

最終的には、上の画像にリストから選択して入れるのがゴール！

2列にすべての要素を書く方法

作り方はコチラ

テーブル形式で要素を書く方法

作り方はコチラ

コメントを残すコメントをキャンセル

Googleフォームを…

自動で作りたい！と思いますよね。スプレッドシートからある程度は自動で作ってくれるようになっていたようなので、GASを使って作成する手順を書いていきます。

※この記事の内容は、Google Apps Script（GAS)を扱える知識が必要です。

※現段階では、質問による分岐、グリッド式の選択肢は作れません。(そのうち更新したいとは思います…）

手順１　スプレッドシートを作成する

フォームのタイトルや説明、質問事項を記入したスプレッドシートを作成します。

実際の入力例は、以下の通り。

作成したExcelを置いておきます。(スプレッドシートに転記するなどしてお使いください。）

サンプルをダウンロード

手順2　シートにApp Scriptを記述する

以下のコードをすべて貼り付けます。

function createForm() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // フォームのタイトルをB1から取得
  const formTitle = sheet.getRange('B1').getDisplayValue();
  // フォームの説明をB2から取得
  const formDescription = sheet.getRange('B2').getDisplayValue();

  const firstRow = 5;
  const lastRow = sheet.getLastRow();
  const dataRows = lastRow - (firstRow - 1);

  //質問の選択肢は、N列(14)までの最大10個
  var questionList = sheet.getRange(firstRow, 1, dataRows, 14).getDisplayValues();

  questionList = questionList.map(question => {
    return {
      title: question[0],       //質問のタイトル
      helpText: question[1],    //質問の説明
      type: question[2],        //回答の種類
      must: question[3],        //必須かどうか
      choices: [question[4], question[5], question[6], question[7], question[8],question[9],question[10],question[11],question[12],question[13]]
    }
  });

  //フォームのタイトルを設定
  const form = FormApp.create(formTitle);
  //フォームの説明を設定
  form.setDescription(formDescription);

  //以下、質問の種類による分岐
  questionList.forEach(question => {

    if (question.type == '記述式'){
      console.log('記述式')
      var choiceItem = form.addTextItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == '段落'){
      console.log('段落')
      var choiceItem = form.addParagraphTextItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == 'ラジオボタン') {
      console.log('ラジオボタン')
      var choiceItem = form.addMultipleChoiceItem();
      var choiceList = [];
      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == 'チェックボックス') {
      console.log('チェックボックス')
      var choiceItem = form.addCheckboxItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == 'プルダウン') {
      console.log('プルダウン')
      var choiceItem = form.addListItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          let choiceObj = choiceItem.createChoice(choice);
          choiceList.push(choiceObj);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setChoices(choiceList);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == '均等目盛') {
      console.log('均等目盛')
      var choiceItem = form.addScaleItem();
      var choiceList = [];

      question.choices.forEach((choice) => {
        if (choice != '') {
          choiceList.push(choice);
        }
      });

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
        .setBounds(choiceList[0],choiceList[1])
        .setLabels(choiceList[2],choiceList[3]);
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      };
    }
    else if (question.type == '日付'){
      console.log('日付')
      var choiceItem = form.addDateItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else if (question.type == '時刻'){
      console.log('時刻')
      var choiceItem = form.addTimeItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
      else if (question.type == '日時'){
      console.log('日時')
      var choiceItem = form.addDateTimeItem();

      choiceItem.setTitle(question.title)
        .setHelpText(question.helpText)
      if (question.must =='必須'){
        choiceItem.setRequired(true)
      }
    }
    else{
      Browser.msgBox('「'+ question.title +'」は作成できない問題形式です');
    }
  });

  //作成したフォームのアドレスをB3セルに入力
  sheet.getRange('B3').setValue(form.getEditUrl());
  Browser.msgBox('「' + formTitle + '」の作成が完了しました');

  // 作成したフォームを開く
  var url = form.getPublishedUrl();
  var script = "<script>window.open('" + url + "', '_blank').focus()</script>";
  var html = HtmlService.createHtmlOutput(script);
  SpreadsheetApp.getUi().showModalDialog(html, '作成したフォームを開きます　\n'+ url);


  // 通常のURLをログに表示
  console.log('URL=  ' +form.getPublishedUrl());
  // 短縮したURLをログに表示
  console.log('短縮URL=  ' + form.shortenFormUrl(form.getPublishedUrl()));

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

function createForm() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// フォームのタイトルをB1から取得

const formTitle = sheet.getRange('B1').getDisplayValue();

// フォームの説明をB2から取得

const formDescription = sheet.getRange('B2').getDisplayValue();

const firstRow = 5;

const lastRow = sheet.getLastRow();

const dataRows = lastRow - (firstRow - 1);

//質問の選択肢は、N列(14)までの最大10個

var questionList = sheet.getRange(firstRow, 1, dataRows, 14).getDisplayValues();

questionList = questionList.map(question => {

return {

title: question[0], //質問のタイトル

helpText: question[1], //質問の説明

type: question[2], //回答の種類

must: question[3], //必須かどうか

choices: [question[4], question[5], question[6], question[7], question[8],question[9],question[10],question[11],question[12],question[13]]

}

});

//フォームのタイトルを設定

const form = FormApp.create(formTitle);

//フォームの説明を設定

form.setDescription(formDescription);

//以下、質問の種類による分岐

questionList.forEach(question => {

if (question.type == '記述式'){

console.log('記述式')

var choiceItem = form.addTextItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '段落'){

console.log('段落')

var choiceItem = form.addParagraphTextItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == 'ラジオボタン') {

console.log('ラジオボタン')

var choiceItem = form.addMultipleChoiceItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == 'チェックボックス') {

console.log('チェックボックス')

var choiceItem = form.addCheckboxItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == 'プルダウン') {

console.log('プルダウン')

var choiceItem = form.addListItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

let choiceObj = choiceItem.createChoice(choice);

choiceList.push(choiceObj);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setChoices(choiceList);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == '均等目盛') {

console.log('均等目盛')

var choiceItem = form.addScaleItem();

var choiceList = [];

question.choices.forEach((choice) => {

if (choice != '') {

choiceList.push(choice);

}

});

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

.setBounds(choiceList[0],choiceList[1])

.setLabels(choiceList[2],choiceList[3]);

if (question.must =='必須'){

choiceItem.setRequired(true)

};

}

else if (question.type == '日付'){

console.log('日付')

var choiceItem = form.addDateItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '時刻'){

console.log('時刻')

var choiceItem = form.addTimeItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else if (question.type == '日時'){

console.log('日時')

var choiceItem = form.addDateTimeItem();

choiceItem.setTitle(question.title)

.setHelpText(question.helpText)

if (question.must =='必須'){

choiceItem.setRequired(true)

}

else{

Browser.msgBox('「'+ question.title +'」は作成できない問題形式です');

}

});

//作成したフォームのアドレスをB3セルに入力

sheet.getRange('B3').setValue(form.getEditUrl());

Browser.msgBox('「' + formTitle + '」の作成が完了しました');

// 作成したフォームを開く

var url = form.getPublishedUrl();

var script = "<script>window.open('" + url + "', '_blank').focus()</script>";

var html = HtmlService.createHtmlOutput(script);

SpreadsheetApp.getUi().showModalDialog(html, '作成したフォームを開きます　\n'+ url);

// 通常のURLをログに表示

console.log('URL= ' +form.getPublishedUrl());

// 短縮したURLをログに表示

console.log('短縮URL= ' + form.shortenFormUrl(form.getPublishedUrl()));

}

サンプルExcelの中にも、コードは記述してあります。

手順3　ボタンにスクリプトを割り当てる

最後に、スプレッドシートのボタンにスクリプトを割り当てれば完成です。

ボタンを押すと、スプレッドシートと同じ場所にフォームが出来ています。

※押したら押した分だけ次々にできるので気を付けましょう…

今後の予定

選択肢によって、違うページに飛ぶようにしたい。技術的には可能です。
しかし、今は一覧でとりあえず作って、分岐は手作業で頑張ります…。
また、グリッド式の質問は、スプレッドシートの記述方法の問題なので、考えればすぐにできそうです。需要があれば作ります。

終わりに…

多くの学校でこういったものはどんどん使われるようになっているのだから、本当は個人で作るのではなく、作成ツールとして教育委員会とかもっと上の方で作ってくれたら嬉しいな…

「Googleフォームを…」に1件のコメントがあります

ピンバック: Googleフォームを自動生成② – ロマンの紅茶の由無し事

コメントを残すコメントをキャンセル

Excel 画像のリンクを一括削除

題名の通り、ネット上の記事をコピー＆ペーストすると、画像にリンクが付いてきたりします。

文字列のリンクだったら、一括削除できますが、画像となると一括削除できません（実装してほしい）。

ということで、下記のコードをVBAに貼り付けて実行でOK！

Sub Macro1()
Dim hlink As Hyperlink
For Each hlink In ActiveSheet.Hyperlinks
    hlink.Delete
Next
End Sub

Sub Macro1()

Dim hlink As Hyperlink

For Each hlink In ActiveSheet.Hyperlinks

hlink.Delete

End Sub

表示してあるシートに対して実行されますのでご注意を。

コメントを残すコメントをキャンセル

マクロで写真を挿入

写真が数枚ならいいけど、何十人もの写真を大きさを揃えて貼り付け！なんてとても手間！！

ただ貼り付けるだけなら、

Range("A1").Select        '貼り付けたいセル
ActiveSheet.Pictures.Insert("ファイルのフルパス").Select

1 2	Range("A1").Select '貼り付けたいセル ActiveSheet.Pictures.Insert("ファイルのフルパス").Select

とVBAで記述すればいいです。あとはファイルパスを繰り返し処理でやらばいいけど…

「セルサイズに合わせて大きさ調整してね！」

なんて気軽に言われると、大変さを知ってほしいなと…

VBAで　写真を挿入→指定のセル範囲に写真をはみ出さないように調整

完成したものがこちらです

Sub pic_take_it()
    Dim pic_name As String
    Dim pic_cell As Range

    '貼り付ける写真とセルの設定
    pic_name = "C:\Users\(ユーザー名)\Desktop\picture.jpg" '写真のフルファイルパス
    Set pic_cell = ActiveSheet.Range(Cells(2, 3), Cells(3, 5))   '貼り付けたいセル範囲 この場合 Range("C2:E3")と同じ

    '貼り付け＆調整マクロを実行
    pic_paste pic_name, pic_cell

End Sub

'###### 貼り付け＆調整用呼び出しマクロ######
Sub pic_paste(pic_name As String, pic_cell As Range)

If Dir(pic_name) <> "" Then     'ファイル名が空白でなければ
    '写真を元の大きさで貼り付ける
    Set myShape = ActiveSheet.Shapes.AddPicture( _
        Filename:=pic_name, _
        LinkToFile:=False, _
        SaveWithDocument:=True, _
        Left:=pic_cell.Left, _
        Top:=pic_cell.Top, _
        Width:=-1, _
        Height:=-1)
    ' ※LinkToFile:=Trueだと、画像リンクのみ作成

    '写真をセルの幅に合わせる
    With myShape
        myHgt = pic_cell.Height     'セルの高さ
        myWdt = pic_cell.Width      'セルの幅
        mySpH = myShape.Height      '写真の高さ
        mySpW = myShape.Width       '写真の幅
            .LockAspectRatio = True
            .Top = pic_cell.Top
            .Left = pic_cell.Left

            'セルと写真の大きさを比べて処理
            If myWdt / mySpW < myHgt / mySpH Then
                Scal = Application.WorksheetFunction.RoundDown(myWdt / mySpW, 3)
            Else
                Scal = Application.WorksheetFunction.RoundDown(myHgt / mySpH, 3)
            End If

            '調整後、ズレることがある（？）ので再度位置調整
            .ScaleWidth Scal, msoFalse, msoScaleFromTopLeft
            '写真を上下中央に配置
            .Top = .Top + (myHgt -  myShape.Height) / 2
            '写真を左右中央に配置
            .Left = .Left + (myWdt -  myShape.Width) / 2
    End With
End If
End Sub

Sub pic_take_it()

Dim pic_name As String

Dim pic_cell As Range

'貼り付ける写真とセルの設定

pic_name = "C:\Users\(ユーザー名)\Desktop\picture.jpg" '写真のフルファイルパス

Set pic_cell = ActiveSheet.Range(Cells(2, 3), Cells(3, 5)) '貼り付けたいセル範囲この場合 Range("C2:E3")と同じ

'貼り付け＆調整マクロを実行

pic_paste pic_name, pic_cell

End Sub

'###### 貼り付け＆調整用呼び出しマクロ######

Sub pic_paste(pic_name As String, pic_cell As Range)

If Dir(pic_name) <> "" Then 'ファイル名が空白でなければ

'写真を元の大きさで貼り付ける

Set myShape = ActiveSheet.Shapes.AddPicture( _

Filename:=pic_name, _

LinkToFile:=False, _

SaveWithDocument:=True, _

Left:=pic_cell.Left, _

Top:=pic_cell.Top, _

Width:=-1, _

Height:=-1)

' ※LinkToFile:=Trueだと、画像リンクのみ作成

'写真をセルの幅に合わせる

With myShape

myHgt = pic_cell.Height 'セルの高さ

myWdt = pic_cell.Width 'セルの幅

mySpH = myShape.Height '写真の高さ

mySpW = myShape.Width '写真の幅

.LockAspectRatio = True

.Top = pic_cell.Top

.Left = pic_cell.Left

'セルと写真の大きさを比べて処理

If myWdt / mySpW < myHgt / mySpH Then

Scal = Application.WorksheetFunction.RoundDown(myWdt / mySpW, 3)

Else

Scal = Application.WorksheetFunction.RoundDown(myHgt / mySpH, 3)

End If

'調整後、ズレることがある（？）ので再度位置調整

.ScaleWidth Scal, msoFalse, msoScaleFromTopLeft

'写真を上下中央に配置

.Top = .Top + (myHgt - myShape.Height) / 2

'写真を左右中央に配置

.Left = .Left + (myWdt - myShape.Width) / 2

End With

End If

End Sub

上のマクロは、「pic_take_it()」を実行すると、その中で、「pic_paste(…)」を呼び出して写真を貼付・調整しています。その方が、何かと便利なので分けました。

※LinkToFile:=Trueだと、画像リンクのみ作成
　についてこれは、保存すると分かりますが、Trueだと画像のリンクのみを貼り、エクセルファイルには画像が保存されません。つまり、写真を挿入後保存し、写真をどこかに移動すると、×印になってしまいます。良い点として、エクセルのファイルサイズが小さく済むことですね。

pic_name = “～\picture.jpg”となっていますが、ファイル名を出席番号にすれば、

pic_name = "写真フォルダのパス" & "\" & n & ".JPG"
※ nを出席番号を表す数式等にする

1 2	pic_name = "写真フォルダのパス" & "\" & n & ".JPG" ※ nを出席番号を表す数式等にする

とすれば、マクロで連続で写真が貼り付けられますよ。

何か分からないことがあれば、フォームでご質問ください。

コメントを残すコメントをキャンセル

pyinstaller使用時のpathについて

こちらに記事が載っています。

pyinstallerで実行ファイル化すると、動作としてテンポラリフォルダ(_MEI*****というフォルダ)に解凍し、そこで実行する挙動になっています。

pyinstallerを使用して一つの実行ファイル化するときに、画像や設定等のファイルを参照する方法は２つあります。
実行ファイルがそれぞれ C:\python\にあるとします。

実行ファイルと同じフォルダに置いて参照する
ファイルを内蔵して参照する

1.実行ファイルと同じフォルダに置いて参照する場合

import sys
import os
get_path =  os.path.dirname(sys.argv[0])
※必要によって　.replace("\\" ,"/")　を追加
print("get_path=",get_path)   #get_dir= C:\python

import sys

import os

get_path = os.path.dirname(sys.argv[0])

※必要によって　.replace("\\" ,"/")　を追加

print("get_path=",get_path) #get_dir= C:\python

2.ファイルを内蔵して参照する場合

実際はテンポラリフォルダ内を参照することになるので、そのパスを取得します。

import sys
import os
temp_path = os.path.dirname(os.path.abspath(__file__))
print("temp_path=",temp_path)    #temp_path= C:\windows\temp\_MEI*****
※実行ファイル化した後での出力の結果です。パスはあくまで例です。

import sys

import os

temp_path = os.path.dirname(os.path.abspath(__file__))

print("temp_path=",temp_path) #temp_path= C:\windows\temp\_MEI*****

※実行ファイル化した後での出力の結果です。パスはあくまで例です。

とりあえずこんな感じでできます。
コードを書く際、内蔵するか否か、最初に決めておかないといけないですね。
そういう意味では、最初の設計はやはり大事です。

コメントを残すコメントをキャンセル

シートを連続作成

シートを連続で作るマクロを書いてみました。

週ごとにシートを作って、管理することはよくあります。学校だと週案などがそうですね。

シートを連携させるために…

前の週の値を参照して、今週分を加算したい。そのためには、以下の条件で元を作ります。

シート名が週を表す数値のみにする
シート名を使って、前の週の値を参照する

1.については、マクロで連続で作成します。

Public Sub add_sheet()
    Dim addedSheet As Worksheet
    Dim n As Long
    Dim w As Long
    
    '作成するシート数
    n = Range("A1").Value　'最初のシートのA1セル（適宜変更）に数値を入力
    For w = 1 To n         '1番から入力した数値まで（1を2にすれば、2から始まります）
        '末尾にシートを追加
        Set addedSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        addedSheet.Name = w
        Next
End Sub

Public Sub add_sheet()

Dim addedSheet As Worksheet

Dim n As Long

Dim w As Long

'作成するシート数

n = Range("A1").Value　'最初のシートのA1セル（適宜変更）に数値を入力

For w = 1 To n '1番から入力した数値まで（1を2にすれば、2から始まります）

'末尾にシートを追加

Set addedSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))

addedSheet.Name = w

End Sub

2.シート名の取得について

VBAでシート名を取得して、B2セルに貼り付ける方法

Public Sub get_sheet_name()
Dim i As Long
Dim num As Long
Dim cell As String

cell = "B2"    'シート名を書き込むセル（適宜変更）
num = 1
For i = 1 To Worksheets.Count
    If i = 1 Then      '最初のシートを飛ばす（設定シートそして活用するため）
        num = num + 1
    Else
        Worksheets(num).Range(cell) = Worksheets(num).Name
        num = num + 1
    End If
Next
End Sub

Public Sub get_sheet_name()

Dim i As Long

Dim num As Long

Dim cell As String

cell = "B2" 'シート名を書き込むセル（適宜変更）

num = 1

For i = 1 To Worksheets.Count

If i = 1 Then '最初のシートを飛ばす（設定シートそして活用するため）

num = num + 1

Else

Worksheets(num).Range(cell) = Worksheets(num).Name

num = num + 1

End If

End Sub

これで、B2セルにシート名が出力されます。

※別の方法としてマクロを使わず、以下の数式でもシート名を取得できます。

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

1	=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

そして、シート名を関数の中で使うにはINDIRECT関数を使います。

B2セルに、シート名
前の番号のシートのC1に参照したい数値が入っている時

=INDIRECT("'"&B2-1&"'!"&"C1")

で参照できます。
※ ”と’が組み合わせてあるので注意

B2セルに、シート名

前の番号のシートのC1に参照したい数値が入っている時

=INDIRECT("'"&B2-1&"'!"&"C1")

で参照できます。

※ ”と’が組み合わせてあるので注意

最後に。マクロを使うか、関数を使うかは好みです。使いやすい方を使うのがいいと思います。

記事のマクロ、関数を使ったファイル以下です。

ダウンロード

コメントを残すコメントをキャンセル

証明写真にカット

学校などで、身分証を自作しているところの一助に！

試用版を下記よりダウンロード(※フォルダ内の3枚まで処理されます）※作成環境の都合上、60MB+αあります

ダウンロード

※認識精度を改善したv1.1に更新しました。

使い方

写真を一人一人撮影します。解像度は640ピクセル以上を推奨。
写真データのファイル名を、学籍番号等に変更します。（後から処理しやすくするため)
本ソフトを起動し、保存してあるフォルダを選択します。
「開始」をクリックすると、先ほどのフォルダ内に「cut」フォルダが自動作成され、中に画像が保存されています。
処理できないファイルがあった場合は、「ERROR.TXT」で内容が確認できます。

微調整の仕方

縦横比を変更できます。書式に合わせて調整をしましょう。
結果が大きすぎたり小さすぎたりするときは、±30％まで領域を加減できます。
上手く認識できないときは、「最小認識サイズ」を調整します。元画像が小さい時は値を小さくします。顔以外も認識してしまう時は、値を大きくします。

画像サイズはバラバラですが、後日調整版を作ります。

作成環境

Windows10、Python3.9.1
またOpenCVを使用しています。

質問等は下記のフォームで連絡ください。

手順１ スプレッドシートを作成する

手順2 シートにApp Scriptを記述する

今後の予定

終わりに…

コメントを残す コメントをキャンセル

やってみよう

終わりに

コメントを残す コメントをキャンセル

やってみよう！

終わりに

コメントを残す コメントをキャンセル

2通りの方法を試しました。

2列にすべての要素を書く方法

テーブル形式で要素を書く方法

コメントを残す コメントをキャンセル

手順１ スプレッドシートを作成する

手順2 シートにApp Scriptを記述する

今後の予定

終わりに…

「Googleフォームを…」に1件のコメントがあります

コメントを残す コメントをキャンセル

コメントを残す コメントをキャンセル

コメントを残す コメントをキャンセル

1.実行ファイルと同じフォルダに置いて参照する場合

2.ファイルを内蔵して参照する場合

コメントを残す コメントをキャンセル

シートを連携させるために…

コメントを残す コメントをキャンセル

使い方

微調整の仕方

作成環境

コメントを残す コメントをキャンセル

手順１　スプレッドシートを作成する

手順2　シートにApp Scriptを記述する

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

手順１　スプレッドシートを作成する

手順2　シートにApp Scriptを記述する

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル

コメントを残すコメントをキャンセル