Sheets function to add content to another sheet

Question

I've been making slow but steady progress on this app that creates the daily bulletin for the school where I teach.

Data is submitted by staff via a form, and is then naturally in a sheet. I already created a script to purge old data from the sheet, thanks in part to help I've gotten here. An additional script orders content on the data sheet by bulletin category, creates a copy of a template sheet, names it by the desired date, puts the date at the top. That's about as far as I've gotten. It also adds the first category heading by default, which is mostly a test.

What I'm attempting to do now is loop through each row of the data sheet to determine if any of the three date columns contains the desired date (entered via a dialog box earlier in the script). If any of them match today's date, we then will check to see if the current category and the category in the row are the same. If they are not, we change the current category and add a new heading to the bulletin sheet. If they are the same, we get the announcement itself and add that to the bulletin sheet. I suspect I'll use embedded functions for these two purposes.

Right now I'm stuck on the loop portion. Again, this should cycle through each row of the data sheet. There are three columns containing the dates (C, D, E). If I can get it to recognize date matches from one of the cells in this range, I can move forward with the rest.

function writeBulletin() {

//get the bulletin date
var bullSheet = todayDay;

//make the bulletin sheet active
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName(todayDate));

//set var for needed sheets
var responses = ss.getSheetByName("Form Responses 1")
var bulletin = ss.getSheetByName(todayDate)    

//get the date from the sheet title and apply it to the date range
var dateCell = bulletin.getRange(3,1);
var sheetDate = bulletin.getName();
dateCell.setValue(sheetDate);
//works

//Now we start building the bulletin
//currentDataRow is a reference to the Responses sheet. Used in later for loop
var currentDataRow = 2;
var currentBulletinRow = 11;
var catCurrent = "01 Administration";

var catCurrentSS=catCurrent.substring(3,30);

var lastRow = responses.getLastRow(); //get last row of data sheet
var lastBull = bulletin.getLastRow();  //get last row of bulletin sheet
var nextBullRow = lastBull+2;
var testOutput = bulletin.getRange(6,3);
var nextBullItem = bulletin.getRange(nextBullRow,1);
nextBullItem.setValue(catCurrentSS);
//testOutput.setValue("dude"); //this works 
if(responses.getRange(2,3).getValue()==todayDate) {
  testOutput.setValue("dude");
}
//bulletin.getRange(2,3).setValue("dude");  //test row
for(var i = 2; i<=lastRow; i++) {
  if(5>3) {
  //if(responses.getRange(i,3).getValue()==sheetDate||responses.getRange(i,4).getValue()==sheetDate||responses.getRange(i,5).getValue()==sheetDate){
  //bulletin.getRange(nextBullRow,3).setValue("dude");//works
  bulletin.getRange(nextBullRow,1).setValue(responses.getRange(i,7).getValue());
    nextBullRow+=2;
}
}

}

I did notice that my loop condition statement had a reversed inequality sign; however, fixing this did not seem to help.

jdv: Good point. fixed it now

What do you mean by "This does not work"? You should be clear in the text what you expect and what results you got. — user1531971

tehhowch tehhowch · Accepted Answer · 2018-03-14T17:59:34

Aside from the issue of repeatedly interacting with the Spreadsheet interface (the alternative being to read values from the Spreadsheet once, then work with the resulting javascript Array object), the issue is that you are comparing a Range object with a String:

var sheetDate = bulletin.getName();

...

  if(responses.getRange(i, 3) == sheetDate || ..... ) {

This will not work :) You need to access the value of the Range:

  if(responses.getRange(i, 3).getValue() == sheetDate || ... ) {

edit: as mentioned in comments, the values in these responses cells are interpreted as Date objects. Date comparisons are fun, because you get to play with time zones and/or format strings. I recommend avoiding needing to use dates in this manner, especially when starting out with scripts.

One possible fix for this new issue is to use the value from dateCell.getValue() after calling SpreadsheetApp.flush() (to ensure the writing of sheetDate is performed first). This will let the spreadsheet do the nasty work making the correct date:

dateCell.setValue(sheetDate);
SpreadsheetApp.flush();
// Construct the proper Date object from the sheetDate value
var compareDate = dateCell.getValue(); 
...
for(var i = 2; i <= lastRow; ++i) {
  // Read into an array [[ 0: elem@(i,3), 1: elem@(i,4), 2: elem@(i,5), 3: elem@(i,6), 4: elem@(i,7) ]]
  var row = responses.getRange(i, 3, 1, 5).getValues();
  if(row[0][0] == compareDate || row[0][1] == compareDate || row[0][2] == compareDate) {
    ...

Sheets function to add content to another sheet

1 Answers