I'm creating a shift table in a spreadsheet.
Currently, I am working on the start time for another attendance management system.

If i can output a list of start times, you can register in bulk, so use Google Apps Script,
I would like to create a list of necessary items from the shift table.

The image looks like the image below, but it is currently stuck at the point where the "start time" is acquired.

The shift table (first image) contains Gantt charts for the day's work, such as "AAAA ..." and "BBBB ..."
I am thinking that if I can get the number of the first column that contains the value, I can get the start time based on that column number.
I don't know the specific method, so I would like advice.

Since the number of staff = the number of rows, there is a concern that it will take time if the number of iterations increases.
It is saved that processing is as simple as possible.

  • Answer # 1


    The following is the flow that comes to mind.
    1. Check the cell values ​​in the X direction from the next column of shift A.
    2. If a character string appears, get the column number of that cell.
    3. The process ends when the character string appears.

    The number of loops per line is the number of blank cells.
    (I think that the upper limit of the number of loops in the for statement is appropriate. Because the processing ends when the character string appears.)

    In the cell situation like the image below, I made a program to get the starting column number.

    As shown in the image below, the column number can be obtained.

    The program is as follows.

    function shiht () {
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet ();
      var sheet = spreadsheet.getActiveSheet ();
      var col = 0;
      var row = 0;
      var value = "";
      // The starting column number is the starting column of the blank cell (because the starting column is the same for all rows)
      // Specify the number of loops more than the maximum number of blank cells in the shift table (appropriately set to 20)
      for (var i = 4;i<= 20;i ++) {
        if (sheet.getRange (1, i) .getValue ()! = "") {
          // If a character appears, get the column number and break!
          value = sheet.getRange (1, i) .getValue ()
          col = i
          row = 1
       Browser.msgBox ("String:" + value + "\\ nStarting column number:" + col + "Column");

Related articles