How do I extract a specific Column from google sheets using Google Script?

Question

So I have an meal inventory spreadsheet that is fed by a google form. Employees count meals at the end of the day every night and each 2nd cell of each column has a title: Initial, Monday, Tuesday you get the idea. I'd like to notify myself when certain items are sold at a fast rate so that they can be replenished. My problem is that I can easily get data from the most recent night using

.getLastColumn();

but I want to base each lastColumn data from the most recent column that has "Initial" in the second cell of the column and put them in a 2 column array on a seperate sheet. any ideas? this is my script so far;

function activeData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = ss.getSheetByName("IREmaildata");
  var lastCol = Sheet.getLastColumn();
  
        var rangeInitial = Sheet.getDataRange(); // Not sure if this would be correct?
        var initial = rangeInitial.getValues();
        var int = "Initial";               //the 3rd cell in "Initial" column must say "Initial".
             
         var rangeLastnight = Sheet.getRange(1,lastCol,37,1); //I can easily get the data from the most recent googleForm submission.
         var lastNight = rangeLastnight.getValues();          //I can easily get the data from the most recent googleForm submission.
       /*
         probably some type of logic will go here?
         
       */  
         ss.getSheetByName("Sheet5").getRange(1,2,37,1).setValues(yesterday);
         ss.getSheetByName("Sheet5").getRange(1,3,37,1).setValues(today);
 
  }

My Spreadsheet; https://docs.google.com/spreadsheets/d/1X_UcqyXXRMyjZ2j46TymMrIMWvt19HOZTTaEUlIVqwE/edit?usp=sharing

Answer 1

• In your shared Spreadsheet, the sheet of IREmaildata and Sheet5 are the input and output you expect, respectively.
• From your shared Spreadsheet, you want to retrieve the values from the sheet IREmaildata.
  • You want to retrieve the last column (Most recent Night Dynamic Column), and retrieve the column (Begining of the week Dynamic Column) which has Initial at the 2nd row and the closest from the last column. And the column "A".
  • And you want to put the values, which were calculated, to new column (Precenatage sold Static Column).
  • For your shared Spreadsheet, you want to retrieve the column "A", "V", "Y" and the calculated values to the other sheet in the Spreadsheet.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

The flow of this sample script is as follows.

1. Retrieve the values from the data range of the sheet IREmaildata.
2. Retrieve the index of the last column from the values.
3. Retrieve the index of the column which has the value of Initial at the row 2.
4. Retrieve the column "A", the index of the column which has the value of Initial at the row 2, the index of the last column.
5. Calculate the percentage and put the value to the array for putting to Spreadsheet.
6. Put the result values to the Spreadsheet.

Sample script:

Please copy and paste the following script to the container-bound script of the shared Spreadsheet. And please prepare the destination sheet and set the destination sheet name to destSheetName, and run the function activeData.

function activeData() {
  var destSheetName = "###";  // Please set the destination sheet name.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("IREmaildata");
  var values = sheet.getDataRange().getValues();
  var lastColumnIndex = values[1].length - 1;
  var initialIndex = 0;
  for (var i = lastColumnIndex - 1; i >= 0; i--) {
    if (values[1][i] == "Initial") {
      initialIndex = i;
      break;
    }
  }
  var result = values.map(function(row, i) {
    var ar = [row[0], row[initialIndex], row[lastColumnIndex]];
    if (i == 0) {
      ar.push("");
    } else if (i == 1) {
      ar.push("Since Begining of week");
    } else if (i > 1) {
      var temp = 1 - (row[lastColumnIndex] / row[initialIndex]);
      ar.push(isNaN(temp) ? "None sold" : temp);
    }
    return ar;
  });
  var destSheet = ss.getSheetByName(destSheetName);
  destSheet.getRange("D3:D").setNumberFormat("0.00%");
  destSheet.getRange(1, 1, result.length, result[0].length).setValues(result);
}

Note:

• At above script, getValues() is used for retrieving the values from the sheet IREmaildata. In this case, the date values are retrieved as the date object. If you want to fix the date format, for example, please modify var values = sheet.getDataRange().getValues(); to var values = sheet.getDataRange().getDisplayValues();.

References:

• map()
• setNumberFormat()