Could not find the error in App Script code for creating rows in Google Sheets and filling-up data based on user inputs

Question

I am trying to create a number of rows in a Sheet after the last row having the specified date (F5 cell value) and based on user inputs in H5, I5, J5, K5 cells (some of them may be blank). User inputs are taken in the Dashboard Sheet, which is attached herewith this post. And the sheet where the data will be stored is specified in F5 having following format:

Date              Student ID      Umail                             Latitude  Longitude Subject
2020-08-20 (G5)   1902011 (H5)    [e-mail] (H5 + K3)    -         -       ICTE4115 (F5)
2020-08-20 (G5)   1902015 (I5)    [e-mail] (I5 + K3)    -         -       ICTE4115 (F5)
2020-08-20 (G5)   1902024 (J5)    [e-mail] (J5 + K3)    -         -       ICTE4115 (F5)
2020-08-20 (G5)   1902036 (K5)    [e-mail] (K5 + K3)    -         -       ICTE4115 (F5)

My codes are: When values in K6 cell selected, the function onePeriod() will be called.

function onePeriod(){
  // For a single Period Class
  var spreadsheet = SpreadsheetApp.getActive();
  var dashboard = spreadsheet.getSheetByName("Dashboard");
  var sheetName = dashboard.getRange("F5").getValue();
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  var umailPattern = dashboard.getRange("K3").getValue();
  
  //Locate the lastrow of the specified date  
  var mDate = dashboard.getRange("G5").getDisplayValue();  
  var startRow = 2;
  var dateColumn = sheet.getRange(startRow,1,sheet.getLastRow(), 1);
  var dates = dateColumn.getDisplayValues().flat();
  var lastRow = dates.lastIndexOf(mDate)+startRow;
    
  //Dashboard is the place for user input where in H5, I5, J5, K5 cell student IDs are entered.  
  var sheet1 = spreadsheet.getSheetByName('Dashboard');
  
  //Need to bring all student IDss from those cells of Dashboard to students array. 
  var data = sheet1.getLastColumn();
  var students = [];
  var students = sheet1.getRange(5, 8, 1, data).getValues();  
  
  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(ssId);
  for (var i=1; i<=students.length; i++){ // you are looping columwise through cells H5, I5, J5, K5 in row 5.
    if (students[i+7][0] !== ''){
      sheet.insertRowAfter(lastRow);
      range1 = sheet.getRange(lastRow+1, 1, 1, 6);
      
      range1.getCell(lastRow+1,1).setValue(dates);
      range1.getCell(lastRow+1,2).setValue(students[i+7][0].toString());
      range1.getCell(lastRow+1,3).setValue(students[i+7][0].toString() + umailPattern);
      range1.getCell(lastRow+1,4).setValue('');
      range1.getCell(lastRow+1,5).setValue('');
      range1.getCell(lastRow+1,6).setValue(sheetName);     
    }
  }  
}

Answer 1

I believe your situation and goal as follows.

• When the cell "K6" is edited, your script of onePeriod() is run. (Do you use OnEdit event trigger?)
• You want to retrieve the values from the cells "K3, F5, G5, H5, I5, J5, K5" from the source sheet ("Dashboard").
• You want to put the converted values to the destination sheet. In this case, the sheet name is the cell "F5". And the sample output situation can be seen at the top of image in your question.
• You want to put the converted values to the next row of last row of the same date in the column "A" on the destination sheet.

In your script, the values are retrieved each cell using getValue, getDisplayValue() and getValues(). And the values are put using setValue in the loop. By this, the process cost will be high. So in this modification, I would like to propose the following flow.

1. Retrieve values from the source sheet.
   • In this case, getDisplayValues() is used for retrieving the values. And when the values are converted, "Student ID" is converted to the number type.
2. Convert the values for putting to the destination sheet.
3. Put the converted values to the destination sheet.

When above flow is reflected to the script, it becomes as follows.

Modified script:

function onePeriod() {
  const srcSheetName = "Dashboard";
  
  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [[,,,,,k3],,[f5,g5,...h5i5j5k5]] = srcSheet.getRange("F3:K5").getDisplayValues();
  
  // 2. Convert the values for putting to the destination sheet.
  const dstValues = h5i5j5k5.reduce((ar, e) => {
    if (e != "") ar.push([g5, Number(e), e + k3, , , f5]);
    return ar;
  }, []);
  
  // 3. Put the converted values to the destination sheet.
  const dstSheet = ss.getSheetByName(f5);
  const dstCurrentValues = dstSheet.getRange(`A2:A${dstSheet.getLastRow()}`).getDisplayValues().flat();
  const index = dstCurrentValues.lastIndexOf(dstValues[0][0]) + 2;
  dstSheet.insertRowsAfter(index, dstValues.length);
  dstSheet.getRange(index + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
}

• When this script is run, the values are put to the next row of the last row of destination sheet.

Note:

• This sample script is for your sample input and output values in your question. So when your actual situation is different from this sample, the script might not work. So please be careful this.

• When I could saw your shared Spreadsheet, I noticed the following 2 important points. Please be careful them.

  1. When I saw it, I noticed that the same function names (2 functions onePeriod()) are used in macros.gs and manualentry.gs.
  2. The last lines of the function onePeriod() in manualentry.gs which uses my script has the scripts which uses dashboard which is not declared.

Reference:

• map()