Process multiple pieces of information, such as team clock-in status, using spreadsheet functions, and notify via email or chat in a list format.

■Overview

・This is an example flow for periodically notifying chat with multiple pieces of information, such as time-stamping information.

・This time, we will introduce a flow that processes time-stamping information accumulated in kintone using spreadsheet functions and notifies it.

‍

■Preparation

・Prepare a database to accumulate time-stamping information in advance. *This time, we will use kintone.

・Prepare a spreadsheet for processing. Please copy and use the actual spreadsheet from the link below. (Please make sure to copy as it is view-only)

https://docs.google.com/spreadsheets/d/1PipZ5rZJPe9Yf0YZjJ5TPQit7oHLpqIgZsDAE92wbhw/edit#gid=1612330990

・Prepare a spreadsheet to obtain the processed values. Please copy and use the actual spreadsheet from the link below.

https://docs.google.com/spreadsheets/d/1PipZ5rZJPe9Yf0YZjJ5TPQit7oHLpqIgZsDAE92wbhw/edit#gid=1666998652

Additionally, by using complex functions this time, we are expanding the range that can be automated with Yoom.

Please check the functions in the above spreadsheet and apply them to your environment.

‍

■Creation Method

① Set a schedule trigger. *This time, it is set to activate at 7 AM every Monday.

② Press the + mark under the trigger, select kintone from the operation to manipulate the database, and test and save after setting the following.

・Action: Retrieve multiple records (up to 500)

・App ID: Enter the specified App ID.

・Conditions for the records you want to retrieve: Enter the specified conditions. Combinations are also possible. (This time, we will retrieve under the condition that it is a specified team and the shared flag is not checked)

*This is a setting to summarize time-stamping information for members of the specified team who do not yet have a shared flag.

③ Press the + mark, select a spreadsheet from the operation to integrate with the app, and test and save after setting the following.

・Action: Enter values in the range

・Spreadsheet ID: Select the prepared spreadsheet ID from the candidates.

・Sheet name: Select the specified sheet name. (In this example, select Case 8_Time-stamping Data Processing Sheet)

・Target cell range: Enter A1:A.

・Values to enter: Add data and embed the employee number and working hours obtained in ②.

・Direction: Column direction

④ Press the + mark, select a spreadsheet from the operation to integrate with the app, and test and save after setting the following.

・Action: Retrieve values

・Spreadsheet ID: Select the prepared spreadsheet ID from the candidates.

・Sheet name: Select the specified sheet name. (In this example, select Case 8_Time-stamper Aggregate Value Retrieval Sheet)

・Target cell range: Enter E2:E.

・Direction: Column direction

⑤ Press the + mark, select an operation to manipulate and transform data, set the following, and test and save.

・Conversion type: Data replacement using regular expressions

・Replacement target: Embed the values obtained in ④.

・Specify replacement location with regular expression: Enter ,.

・Replacement string: Enter .

⑥ Press the + mark, select Slack from the operation to integrate with the app, and test and save after setting the following.

・Action: Send a message to the channel

・Channel ID to post to: Enter the specified channel ID.

・Message content: Embed the values converted in ⑤.

⑦ Press the + mark, select an operation to repeat the same process, set the following, and test and save.

・Operation: Select ②.

・Output: Select Record ID.

⑧ Press the + mark on the loop side, select kintone from the operation to manipulate the database, and test and save after setting the following.

・Action: Update record

・App ID: Enter the specified App ID.

・Conditions for the record you want to update: Match the field code to the record number and embed the {{Record ID loop variable}} obtained in ⑦.

・Field to update: Update by setting a flag in fields such as shared.