[corp-engr] The Art of Not Programming

Introduction

Of course, for people who can program, their primary solution is programming. While programming is versatile, every tool has its own characteristics, meaning it has both strengths and weaknesses.
For example, when I receive a request to develop a tool, I often approach it in the following order:

1. Omit the task itself if it's redundant.
2. Use spreadsheet formulas if they can replace the need for code.
3. Perform programming.

1. Omit the task itself if it's redundant

Suppose there is a task to "regularly create and submit reports from Salesforce." However, in some cases, the analysis screen within Salesforce is already sufficient.
In such situations, streamlining the report creation process only increases waste. It is better to trace the operation from a data perspective to eliminate the task itself.
However, if the task cannot be removed, making such a suggestion can cause a breakdown in communication, so I often listen with a straight face during the interview.

2. Use spreadsheet formulas if they can replace the need for code

From this point on, we enter the phase of creating a tool, but not everything is solved by programming. If it can be replaced by spreadsheet formulas as much as possible, it should be for the following reasons:

• Lower learning cost
  • Consequently, it requires less effort to fix when it breaks.
• Ability to adapt to changes in the table (such as adding columns).

Example

Flow

1. Copy the month part and paste it into an empty row. Since the month part contains a formula that calculates "the date one month after the cell above," it will automatically populate with the following month.
   
2. The cells in the table are set to "output a result if there is information in column A." Perform the tasks according to the information shown in those cells.
   
   
   
   

Since these types of ARRAYFORMULA functions can be used in other sheets as well, they are often used in workflows like this:

1. Have someone import a CSV downloaded from SaaS_A into Sheet A.
2. Output the correctly formatted information into Sheet B via formulas.
3. Have someone import Sheet B into SaaS_B.

While it also involves some Google Apps Script (GAS), I sometimes use custom functions as well.
https://developers.google.com/apps-script/guides/sheets/functions?hl=ja

For example, when you want to use a formula that is incompatible with ARRAYFORMULA in a situation where you were trying to perform a bulk conversion using ARRAYFORMULA.
In such cases, you can process it by passing the list as an argument, as shown in the example below:

function EMPLOYED_LIST(list, ignoreList){
  return list.filter(row => !ignoreList.some(ignoreRow => ignoreRow[SHEET.ignoreCrew.column.empCode - 1] === row[SHEET.crew.column.empCode - 1])
    && row[SHEET.crew.column.status - 1] === CREW.status.employed
  );
}

The Downside of "God Spreadsheets"

While it's better to use spreadsheets if they can serve as a substitute, spreadsheets have a dangerous pitfall. As you keep adding formulas, they can turn into a "God Spreadsheet" (overly complex spreadsheets) that no one dares to touch.
When that happens, it's no longer just a tool but becomes technical debt for the organization itself. Even a minor specification change will exhaust everyone involved. It is preferable to keep spreadsheets as focused on a single responsibility as possible.

3. Perform Programming

When a task absolutely cannot be eliminated (or changed) and is beyond the control of a spreadsheet, it is time for programming.

Examples

• Cases that cannot be solved with formulas (e.g., sending messages to Slack)
• Reducing sheet load (programming may be used when employing many high-load formulas, such as having an importxml in every cell)
• Connecting with external APIs
• Complex business logic or state management is required

Conclusion

Programming is not the goal; it is a means to an end, and it just happens to be the one we are most proficient at. Just as you cannot plow a field with a sports car, it is vital to choose the appropriate (and cost-effective) tool, and this is something we must keep in mind.