코딩 수준: 초급
소요 시간: 5분
프로젝트 유형: 맞춤 메뉴를 사용한 자동화
목표
- 솔루션의 작동 방식을 이해합니다.
- 솔루션 내에서 Apps Script 서비스가 하는 작업을 이해합니다.
- 스크립트를 설정합니다.
- 스크립트를 실행합니다.
이 솔루션 정보
엔드 투 엔드 오프사이트 활동 등록 시스템을 만듭니다. 이 솔루션은 직원이 활동 환경설정을 표현할 수 있는 양식을 만들고 직원 환경설정을 활동 일정에 일치시킵니다.
작동 방식
이 스크립트는 Google Sheets의 활동 일정을 사용하여 직원이 활동 환경설정을 선택할 수 있는 Google 양식을 만듭니다. 응답이 있으면 스크립트는 직원 선호도를 각 활동의 일정 및 수용 인원과 일치시킵니다. 일치 항목은 직원별로 정리된 시트와 활동별로 정리된 시트 등 두 가지 새 시트로 제공됩니다.
Apps Script 서비스
이 솔루션은 다음 서비스를 사용합니다.
- 스프레드시트 서비스: 활동 일정 및 양식 응답을 보관하고 직원에게 활동을 할당합니다.
- Forms 서비스: 직원이 활동 환경설정을 입력할 수 있는 양식을 만듭니다.
- 유틸리티 서비스: 문자열과 날짜 형식을 지정합니다.
기본 요건
이 샘플을 사용하려면 다음 기본 요건이 필요합니다.
- Google 계정 (Google Workspace 계정의 경우 관리자 승인이 필요할 수 있음)
- 인터넷에 액세스할 수 있는 웹브라우저
스크립트 설정
다음 버튼을 클릭하여 오프사이트 활동 가입 샘플 스프레드시트의 사본을 만듭니다. 이 솔루션의 Apps Script 프로젝트가 스프레드시트에 첨부되어 있습니다.
사본 만들기
스크립트 실행
- 사본으로 만든 스프레드시트에서 활동 > 양식 만들기를 클릭합니다. 이 맞춤 메뉴가 표시되도록 페이지를 새로고침해야 할 수도 있습니다.
메시지가 표시되면 스크립트를 승인합니다. OAuth 동의 화면에 이 앱은 확인되지 않았습니다라는 경고가 표시되면 고급 > {프로젝트 이름}으로 이동(안전하지 않음)을 선택하여 계속 진행합니다.
활동 > 양식 만들기를 다시 클릭합니다.
테스트 응답을 생성하려면 활동 > 테스트 데이터 생성을 클릭합니다.
양식을 직접 테스트하려면 Tools > Manage form > Go to live form을 클릭합니다.
양식을 작성한 후 제출합니다.
스프레드시트에서 활동 > 활동 할당을 클릭합니다.
활동별 사용자 및 활동 명단이라는 두 가지 새로운 시트를 검토합니다.
코드 검토
이 솔루션의 Apps Script 코드를 검토하려면 아래의 소스 코드 보기를 클릭합니다.
소스 코드 보기
Code.gs
solutions/automations/offsite-activity-signup/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/offsite-activity-signup
/*
Copyright 2022 Google LLC
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
https://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
const NUM_ITEMS_TO_RANK = 5;
const ACTIVITIES_PER_PERSON = 2;
const NUM_TEST_USERS = 150;
/**
* Adds custom menu items when opening the sheet.
*/
function onOpen() {
let menu = SpreadsheetApp.getUi().createMenu('Activities')
.addItem('Create form', 'buildForm_')
.addItem('Generate test data', 'generateTestData_')
.addItem('Assign activities', 'assignActivities_')
.addToUi();
}
/**
* Builds a form based on the "Activity Schedule" sheet. The form asks attendees to rank their top
* N choices of activities, where N is defined by NUM_ITEMS_TO_RANK.
*/
function buildForm_() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
if (ss.getFormUrl()) {
let msg = 'Form already exists. Unlink the form and try again.';
SpreadsheetApp.getUi().alert(msg);
return;
}
let form = FormApp.create('Activity Signup')
.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId())
.setAllowResponseEdits(true)
.setLimitOneResponsePerUser(true)
.setCollectEmail(true);
let sectionHelpText = Utilities.formatString('Please choose your top %d activities',
NUM_ITEMS_TO_RANK);
form.addSectionHeaderItem()
.setTitle('Activity choices')
.setHelpText(sectionHelpText);
// Presents activity ranking as a form grid with each activity as a row and rank as a column.
let rows = loadActivitySchedule_(ss).map(function(activity) {
return activity.description;
});
let columns = range_(1, NUM_ITEMS_TO_RANK).map(function(value) {
return Utilities.formatString('%s', toOrdinal_(value));
});
let gridValidation = FormApp.createGridValidation()
.setHelpText('Select one item per column.')
.requireLimitOneResponsePerColumn()
.build();
form.addGridItem()
.setColumns(columns)
.setRows(rows)
.setValidation(gridValidation);
form.addListItem()
.setTitle('Assign other activities if choices are not available?')
.setChoiceValues(['Yes', 'No']);
}
/**
* Assigns activities using a random priority/random serial dictatorship approach. The results
* are then populated into two new sheets, one listing activities per person, the other listing
* the rosters for each activity.
*
* See https://en.wikipedia.org/wiki/Random_serial_dictatorship for additional information.
*/
function assignActivities_() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let activities = loadActivitySchedule_(ss);
let activityIds = activities.map(function(activity) {
return activity.id;
});
let attendees = loadAttendeeResponses_(ss, activityIds);
assignWithRandomPriority_(attendees, activities, 2);
writeAttendeeAssignments_(ss, attendees);
writeActivityRosters_(ss, activities);
}
/**
* Selects activities via random priority.
*
* @param {object[]} attendees - Array of attendees to assign activities to
* @param {object[]} activities - Array of all available activities
* @param {number} numActivitiesPerPerson - Maximum number of activities to assign
*/
function assignWithRandomPriority_(attendees, activities, numActivitiesPerPerson) {
let activitiesById = activities.reduce(function(obj, activity) {
obj[activity.id] = activity;
return obj;
}, {});
for (let i = 0; i < numActivitiesPerPerson; ++i) {
let randomizedAttendees = shuffleArray_(attendees);
randomizedAttendees.forEach(function(attendee) {
makeChoice_(attendee, activitiesById);
});
}
}
/**
* Attempts to assign an activity for an attendee based on their preferences and current schedule.
*
* @param {object} attendee - Attendee looking to join an activity
* @param {object} activitiesById - Map of all available activities
*/
function makeChoice_(attendee, activitiesById) {
for (let i = 0; i < attendee.preferences.length; ++i) {
let activity = activitiesById[attendee.preferences[i]];
if (!activity) {
continue;
}
let canJoin = checkAvailability_(attendee, activity);
if (canJoin) {
attendee.assigned.push(activity);
activity.roster.push(attendee);
break;
}
}
}
/**
* Checks that an activity has capacity and doesn't conflict with previously assigned
* activities.
*
* @param {object} attendee - Attendee looking to join the activity
* @param {object} activity - Proposed activity
* @return {boolean} - True if attendee can join the activity
*/
function checkAvailability_(attendee, activity) {
if (activity.capacity <= activity.roster.length) {
return false;
}
let timesConflict = attendee.assigned.some(function(assignedActivity) {
return !(assignedActivity.startAt.getTime() > activity.endAt.getTime() ||
activity.startAt.getTime() > assignedActivity.endAt.getTime());
});
return !timesConflict;
};
/**
* Populates a sheet with the assigned activities for each attendee.
*
* @param {Spreadsheet} ss - Spreadsheet to write to.
* @param {object[]} attendees - Array of attendees with their activity assignments
*/
function writeAttendeeAssignments_(ss, attendees) {
let sheet = findOrCreateSheetByName_(ss, 'Activities by person');
sheet.clear();
sheet.appendRow(['Email address', 'Activities']);
sheet.getRange('B1:1').merge();
let rows = attendees.map(function(attendee) {
// Prefill row to ensure consistent length otherwise
// can't bulk update the sheet with range.setValues()
let row = fillArray_([], ACTIVITIES_PER_PERSON + 1, '');
row[0] = attendee.email;
attendee.assigned.forEach(function(activity, index) {
row[index + 1] = activity.description;
});
return row;
});
bulkAppendRows_(sheet, rows);
sheet.setFrozenRows(1);
sheet.getRange('1:1').setFontWeight('bold');
sheet.autoResizeColumns(1, sheet.getLastColumn());
}
/**
* Populates a sheet with the rosters for each activity.
*
* @param {Spreadsheet} ss - Spreadsheet to write to.
* @param {object[]} activities - Array of activities with their rosters
*/
function writeActivityRosters_(ss, activities) {
let sheet = findOrCreateSheetByName_(ss, 'Activity rosters');
sheet.clear();
var rows = [];
var rows = activities.map(function(activity) {
let roster = activity.roster.map(function(attendee) {
return attendee.email;
});
return [activity.description].concat(roster);
});
// Transpose the data so each activity is a column
rows = transpose_(rows, '');
bulkAppendRows_(sheet, rows);
sheet.setFrozenRows(1);
sheet.getRange('1:1').setFontWeight('bold');
sheet.autoResizeColumns(1, sheet.getLastColumn());
}
/**
* Loads the activity schedule.
*
* @param {Spreadsheet} ss - Spreadsheet to load from
* @return {object[]} Array of available activities.
*/
function loadActivitySchedule_(ss) {
let timeZone = ss.getSpreadsheetTimeZone();
let sheet = ss.getSheetByName('Activity Schedule');
let rows = sheet.getSheetValues(
sheet.getFrozenRows() + 1, 1,
sheet.getLastRow() - 1, sheet.getLastRow());
let activities = rows.map(function(row, index) {
let name = row[0];
let startAt = new Date(row[1]);
let endAt = new Date(row[2]);
let capacity = parseInt(row[3]);
let formattedStartAt= Utilities.formatDate(startAt, timeZone, 'EEE hh:mm a');
let formattedEndAt = Utilities.formatDate(endAt, timeZone, 'hh:mm a');
let description = Utilities.formatString('%s (%s-%s)', name, formattedStartAt, formattedEndAt);
return {
id: index,
name: name,
description: description,
capacity: capacity,
startAt: startAt,
endAt: endAt,
roster: [],
};
});
return activities;
}
/**
* Loads the attendeee response data.
*
* @param {Spreadsheet} ss - Spreadsheet to load from
* @param {number[]} allActivityIds - Full set of available activity IDs
* @return {object[]} Array of parsed attendee respones.
*/
function loadAttendeeResponses_(ss, allActivityIds) {
let sheet = findResponseSheetForForm_(ss);
if (!sheet || sheet.getLastRow() == 1) {
return undefined;
}
let rows = sheet.getSheetValues(
sheet.getFrozenRows() + 1, 1,
sheet.getLastRow() - 1, sheet.getLastRow());
let attendees = rows.map(function(row) {
let _ = row.shift(); // Ignore timestamp
let email = row.shift();
let autoAssign = row.pop();
// Find ranked items in the response data.
let preferences = row.reduce(function(prefs, value, index) {
let match = value.match(/(\d+).*/);
if (!match) {
return prefs;
}
let rank = parseInt(match[1]) - 1; // Convert ordinal to array index
prefs[rank] = index;
return prefs;
}, []);
if (autoAssign == 'Yes') {
// If auto assigning additional activites, append a randomized list of all the activities.
// These will then be considered as if the attendee ranked them.
let additionalChoices = shuffleArray_(allActivityIds);
preferences = preferences.concat(additionalChoices);
}
return {
email: email,
preferences: preferences,
assigned: [],
};
});
return attendees;
}
/**
* Simulates a large number of users responding to the form. This enables users to quickly
* experience the full solution without having to collect sufficient form responses
* through other means.
*/
function generateTestData_() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = findResponseSheetForForm_(ss);
if (!sheet) {
let msg = 'No response sheet found. Create the form and try again.';
SpreadsheetApp.getUi().alert(msg);
}
if (sheet.getLastRow() > 1) {
let msg = 'Response sheet is not empty, can not generate test data. ' +
'Remove responses and try again.';
SpreadsheetApp.getUi().alert(msg);
return;
}
let activities = loadActivitySchedule_(ss);
let choices = fillArray_([], activities.length, '');
range_(1, 5).forEach(function(value) {
choices[value] = toOrdinal_(value);
});
let rows = range_(1, NUM_TEST_USERS).map(function(value) {
let randomizedChoices = shuffleArray_(choices);
let email = Utilities.formatString('person%d@example.com', value);
return [new Date(), email].concat(randomizedChoices).concat(['Yes']);
});
bulkAppendRows_(sheet, rows);
}
/**
* Retrieves a sheet by name, creating it if it doesn't yet exist.
*
* @param {Spreadsheet} ss - Containing spreadsheet
* @Param {string} name - Name of sheet to return
* @return {Sheet} Sheet instance
*/
function findOrCreateSheetByName_(ss, name) {
let sheet = ss.getSheetByName(name);
if (sheet) {
return sheet;
}
return ss.insertSheet(name);
}
/**
* Faster version of appending multiple rows via ranges. Requires all rows are equal length.
*
* @param {Sheet} sheet - Sheet to append to
* @param {Array<Array<object>>} rows - Rows to append
*/
function bulkAppendRows_(sheet, rows) {
let startRow = sheet.getLastRow() + 1;
let startColumn = 1;
let numRows = rows.length;
let numColumns = rows[0].length;
sheet.getRange(startRow, startColumn, numRows, numColumns).setValues(rows);
}
/**
* Copies and randomizes an array.
*
* @param {object[]} array - Array to shuffle
* @return {object[]} randomized copy of the array
*/
function shuffleArray_(array) {
let clone = array.slice(0);
for (let i = clone.length - 1; i > 0; i--) {
let j = Math.floor(Math.random() * (i + 1));
let temp = clone[i];
clone[i] = clone[j];
clone[j] = temp;
}
return clone;
}
/**
* Formats an number as an ordinal.
*
* See: https://stackoverflow.com/questions/13627308/add-st-nd-rd-and-th-ordinal-suffix-to-a-number/13627586
*
* @param {number} i - Number to format
* @return {string} Formatted string
*/
function toOrdinal_(i) {
let j = i % 10;
let k = i % 100;
if (j == 1 && k != 11) {
return i + 'st';
}
if (j == 2 && k != 12) {
return i + 'nd';
}
if (j == 3 && k != 13) {
return i + 'rd';
}
return i + 'th';
}
/**
* Locates the sheet containing the form responses.
*
* @param {Spreadsheet} ss - Spreadsheet instance to search
* @return {Sheet} Sheet with form responses, undefined if not found.
*/
function findResponseSheetForForm_(ss) {
let formUrl = ss.getFormUrl();
if (!ss || !formUrl) {
return undefined;
}
let sheets = ss.getSheets();
for (let i in sheets) {
if (sheets[i].getFormUrl() === formUrl) {
return sheets[i];
}
}
return undefined;
}
/**
* Fills an array with a value ([].fill() not supported in Apps Script).
*
* @param {object[]} arr - Array to fill
* @param {number} length - Number of items to fill.
* @param {object} value - Value to place at each index.
* @return {object[]} the array, for chaining purposes
*/
function fillArray_(arr, length, value) {
for (let i = 0; i < length; ++i) {
arr[i] = value;
}
return arr;
}
/**
* Creates and fills an array with numbers in the range [start, end].
*
* @param {number} start - First value in the range, inclusive
* @param {number} end - Last value in the range, inclusive
* @return {number[]} Array of values representing the range
*/
function range_(start, end) {
let arr = [start];
let i = start;
while (i < end) {
arr.push(i += 1);
}
return arr;
}
/**
* Transposes a matrix/2d array. For cases where the rows are not the same length,
* `fillValue` is used where no other value would otherwise be present.
*
* @param {Array<Array<object>>} arr - 2D array to transpose
* @param {object} fillValue - Placeholder for undefined values created as a result
* of the transpose. Only required if rows aren't all of equal length.
* @return {Array<Array<object>>} New transposed array
*/
function transpose_(arr, fillValue) {
let transposed = [];
arr.forEach(function(row, rowIndex) {
row.forEach(function(col, colIndex) {
transposed[colIndex] = transposed[colIndex] || fillArray_([], arr.length, fillValue);
transposed[colIndex][rowIndex] = row[colIndex];
});
});
return transposed;
}
참여자
이 샘플은 Google에서 Google 개발자 전문가의 도움을 받아 유지관리합니다.