Group-Reservation-Information-Management-System-for-Accommodation

Summary

At the time of receiving the reservation of the group, the meal information of the group is appropriately changed in format and compiled as a "calendar" or added to the "shift table". If there is a change, change one place and everything will be updated. Furthermore, since the execution time of all GAS code exceeds two minutes, the execution time of all gas is reduced by about 26 times by speeding up.

Required Environment

The formad used for the work should be the following three spreadsheets.

• Group Customer Input Spreadsheet (For customers to enter reservation information)
• Group Progress Spread Sheet (For employees to manage their organizations.)
• Shift Sheet (For employees to manage their work hours.)

Copy them to your own Google Drive and embed the GAS program into Group Progress Spread Sheet. (you will need to put the spreadsheet key in the designated place in the code).

Currently, access is restricted and not all of them are available for viewing. If necessary, some of them will be introduced in the descriptions below.

Demonstration

• When a reservation is made, enter the name of the group, check-in date, check-out date, etc. on the ~month_Front Desk Sheet in the Group Progress Spread Sheet.

---

• After the client has submitted the Spreadsheet (Group Customer Input Spreadsheet) with his/her lodging information, copy the last page of the Spreadsheet, prepare a copy of the "Original Employee Use (従業員使用欄の原本)" sheet in the Group Progress Spread Sheet as a foundation, and stick it to it.

---

• If there is a new reservation, a change in the number of people, or a change in the number of meals, press the Update button, and the changes will be reflected in the Calendar in the Group Progress Spread Sheet and shift Sheets.

Procedure (details of Demonstration Video)

1. After accepting the reservation, enter the name of the group, tentative number of people, etc. in the "○月_フロント用シート"("○month_Front Desk Sheet") in the Group Progress Spread Sheet. (The reservation month corresponds to ○.)

※ If this is the first reservation for the month, copy and rename the master sheet.

---

2. After a group reservation is made, you will be asked to enter the information of the group guests in a formad like the spreadsheet → Group Customer Input Spreadsheet.

• ↑ Notes to customers, etc ↑

---

• ↑ Customer enters meal times and their number ↑

---

• Important information such as meal times, number of meals, etc., is collected here. (Referenced by spreadsheet functions like " ' = Sheet name'! $A$1 ") ↑

The above is a partial reference to Group Customer Input Spreadsheet.

---

2. The system is designed to automatically consolidate important information entered by group guests, such as the number of meals and their times, into a single sheet. (This is implemented using only functions in the spreadsheet.) This sheet is named "従業員使用欄" in this case, and this sheet in the Group Customer Input Spreadsheet.

• Example group (Group_A)

From now on, this sheet will be referred to as the Employee Use Sheet.(the Employee Use Sheet is one of several sheets existing in Group Customer Input Spreadsheet.)

---

3. Copy this sheet (named "従業員使用欄) into the spreadsheet (Group Progress Spread Sheet.) that controls all associations. This copied sheet will be the source of data to be posted, and will be the sheet to be changed when the number of members changes in the future.

従業員使用欄 (Employee Use Sheet) --(copy)--> Group Progress Spread Sheet.

---

4. Press the "Update" button on the "実行ボタン" sheet in the Group Progress Spread Sheet. The below is a partial reference to Group Progress Spread Sheet. The data is then formatted to fit the meal management calendar and employee shift schedules that will be captured later, and is automatically transcribed.

• Above is a view of the sheet with the update button in Group Progress Spread Sheet

---

• Above is a sheet to manage reservation groups by month. (Four groups were demonstrated using January and February as examples)

---

• The above is a sheet for managing the meal information of a reserved group by date. By arranging them by date key in this way, the number of meals to be prepared by the staff on a given day becomes clear, and it is also easier to reflect this in the shifts of employees who are lined up by date. (Four groups were demonstrated using January and February as examples)

---

• The above captures the group information posted to the employee's shift schedule, which is posted from the calendar. This allows the user to check shifts while keeping track of the group's status.(Four groups were demonstrated using January as examples)

---

If a new group makes a reservation, perform step (1), and if the number of people in an existing group changes, perform step (4) only.

Other fine features

Custom Tab

Custom tabs have been added at the top of the page to allow "update functions" from any page, update emails, and saving or deleting last month's group sheets.

• Update ("すべて更新")

  • You can click the Update button, i.e., update the sheet, on any page.

• Send an email update ("更新メールを送る")

• When you make a change to your organization's information on a page, you can send an update e-mail to Chatwork on the spot.
  ※ Account token and room ID must be given to the program

• Copy last month's individual group materials to the Drive. ("先月分の団体個別資料を保存用へ移動")

  • After the stay, materials that are no longer needed can be saved to any location on the drive for archival purposes.
    ※ Specify the save location by specifying the Google Drive key in the program. ※ At this time, the reservation group before the month specified in the "Execute button (実行ボタン)" sheet is targeted.

• Delete individual group documents that are no longer needed from this spreadsheet (Group Progress Spread Sheet).

  • After the stay, materials that are no longer needed can be saved to any location on the Drive for archival purposes.
    ※ At this time, the reservation group before the month specified in the "Execute button (実行ボタン)" sheet is targeted.
    ※ Before performing this operation, you must copy the sheets to the Drive by executing "Copy last month's individual group documents to storage ("先月分の団体個別資料を保存用へ移動")" or you will not be able to view the sheets again.