0

I have a spreadsheet for various departments and I am trying to create forms (in spreadsheet form) for each department populating the number of staff in each department and the classes they teach.

I have created a function to copy this data, however, it doesn't stop when it reaches a blank but continues on for the rest of the column. There was a similar question that had been asked, however, that searches the entire column rather than starting at a specific point.

I feel like I am confusing the issue a little so to give an example:

English department data starts at row 3, the staff details are in column H up to row 20.

The the Maths department data starts at row 30, the staff details are up to row 45.

My function needs to pick up English in column A, move to column H count the number of cells until row 20, and copy these values into the form I am setting up.

I have created a function that finds the department name, and a function that loops through all the subjects, I just can't find a way to search up to the first blank cell from row x.

The code I found in another question is from Don Kirkby as follows:

 function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }
  return (ct);
}

Just to add the function needs to be used in the function below, so copy all values until it hits a blank and paste those values into the form:

function countTargetTeacher(myRowOfTarget) {
  var teachRow = targetSheet.getRange(myRowOfTarget, 8).getValues();
  var targetTeacherRange = targetSheet.getRange(myRowOfTarget, 8, teachLastRow, 3).getValues();
  sheet.insertRowsAfter(teacherRow, teachLastRow);
  sheet.getRange(teacherRow + 1, 1, teachLastRow, 3).setValues(targetTeacherRange);
  // deleteBlankRows()  // https://stackoverflow.com/questions/43522602/filter-data-by-column-k-in-google-script-editor-google-sheets
}

Sorry for the long winded post, any help would be appreciated.

Thanks

Fazila

Fazila
  • 71
  • 7
  • Can you share your spreadsheet so I can do a real test and provide you a working apps script example? – Jeff Rush Mar 04 '20 at 15:45
  • Hi Jeff, unfortunately I can't share the spreadsheet the organisation has set restrictions. The code below seems to work at present but thank you for looking at this. Thanks – Fazila Mar 06 '20 at 10:14

1 Answers1

0

I've been playing around with it and had some code that I had used earlier which I tweaked slightly which looks promising so far:

function countCellForTargetSheet(myRowOfTarget, column) {
  var columnRange = targetSheet.getRange(myRowOfTarget, column, targetSheetLength);
  var columnVals = columnRange.getValues();

  var lastCell = myRowOfTarget

  for (i = 1; i < columnVals.length; i++) {
    if (columnVals[i][0] == "") {
      break;
    } else {
      lastCell = lastCell + 1;
    }
  };

  return lastCell;
}

:)

Fazila
  • 71
  • 7