Same message repeating 3 times

Question

I have code below that takes the first column and prints it in a google doc based on the date. Unfortunately, this repeats and prints the row three times.

function saveAsDOC() {
  var fileName = "Announcements Test File";
  var doc = DocumentApp.create(fileName);

  // Get the range in the spreadsheet
  var ws = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  try {
    var data = ws.getValues();

    // Announcements loop
    if (data.length > 1) { // Make sure data is longer than one character
      for (var row = 1; row < data.length; row++) {
        if(new Date(data[row][2]) <= new Date()) { // Check the date
          for (var col = 1; col < data[row].length; col++) {
            if(data[row][col]!=""){ // Make sure the data is not empty
              doc.appendParagraph(data[row][1]); // Append announcement
              doc.appendParagraph("") // Line break between announcements
            }
          }
        }
      }
    }
   }

  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

The source data is in a Google spreadsheet, with three columns "Announcement", "Start date" and "End date". I want to compile a list of announcements for "today" (i.e. today's date is between the start & end dates, inclusively).

                A                     B             C
1 | Announcement               | Start date  | End date    |
2 | You SHOULD see this.       | 1 Jan 2015  | 31 Dec 2015 |
3 | You SHOULD NOT see this.   | 1 Jan 2015  | 1 Jan 2015  |
4 | You SHOULD ALSO see this.  | 1 Jan 2015  | 31 Dec 2015 |
5 | You SHOULD NOT see this.   | 31 Dec 2015 | 31 Dec 2015 |

Here is an example of my output:

My code is skipping the announcements that should not bee seen, that's good. But it's printing the expected announcements multiple times. How can I fix that?

Your question isn't clear, you need to [edit] in some more info, and reduce the code to a [mcve]. What does your data look like? (An example that produces your problem would help.) The creation of a doc is irrelevant to the problem you're facing, take it out, and use a simpler way to track execution of the inner loop, like a log. Likewise, no need for any UI part (`toast`). If you make the _core_ problem easily reproducible, you'll get better answers... and possibly figure it out yourself! — Mogsdad, Aug 27 '15 at 19:52
I've updated my post. I'm clear on how to post questions, as I unfortunately do it all the time and get no answers. — tda, Aug 27 '15 at 20:16
Try this. Pick any row in your spreadsheet that has an announcement that currently shows up 3 times in your document. Add another cell at the end of the row, with anything in it. Then run the script. You should see 4 copies of that announcement now. — Mogsdad, Aug 27 '15 at 20:22
I've done so and it prints 4 copies. I'm assuming the row `for (var row = 1; row < data.length; row++) {` has something to do with it? — tda, Aug 27 '15 at 20:31
Part, yes - that loops over all cells in the row. Combined with the comparison that checks whether a cell is empty, you'll end up printing _something_ for every non-empty cell. And that something is always the _same thing_ because you append `data[row][1]`. — Mogsdad, Aug 27 '15 at 20:34
That was the way I was told to select the first column, is there a workaround? — tda, Aug 27 '15 at 20:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/88129/discussion-between-mogsdad-and-techteej). — Mogsdad, Aug 27 '15 at 20:41

score 0 · Accepted Answer · edited Jan 18 '21 at 12:35

Here's a minimal version of your code that demonstrates the problem, with comments added explaining what's happening:

function announcements() {
  // Get the range in the spreadsheet
  var ws = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  var data = ws.getValues();
  var results = [];

  if (data.length > 1) {                                    // Make sure data is longer than one ROW (not character)
    for (var row = 1; row < data.length; row++) {           // Skips first row, then loop over the rest is that what you want?
      if(new Date(data[row][2]) <= new Date()) {            // Check the date, [2] is the third column.
        for (var col = 1; col < data[row].length; col++) {  // Loop over all columns except the first one
          if(data[row][col]!=""){                           // Make sure the data is not empty
            results.push(data[row][1]);                     // Append [row][1]; **** Same value
          }
        }
      }
    }
  }

  Logger.log( JSON.stringify( results ) );
}

You've got a few possible problems here:

if (data.length > 1) checks that you have more than one row of data, which mismatches your comment. Make sure it's what you want to do.
for (var row = 1; etc. loops over all rows, but skips the first row. Maybe that's what you want, but a common problem the folks run into is that the spreadsheet rows (and columns) are numbered starting at 1 when you use Google's Spreadsheet methods, but from 0 in JavaScript arrays. The content of data is a JavaScript array, so it starts from 0.(Discussed in chat, this was understood, so no problem there.)
if(new Date(data[row][2]) <= new Date()) only checks if the "End date" is a date up to an including today. You also need to check the "Start date". (Discussed in chat.)
for (var col = 1; col < data[row].length; col++) loops over all the columns (in each row). Then if(data[row][col]!="") checks if that cell is non-blank, and results.push(data[row][1]) appends the "announcement". This is why you're seeing repeated announcements. According to your problem statement, there is no need to loop across the row, you just want to check whether there is an announcement for each row.

Updated code

function announcements() {
  // Get the range in the spreadsheet
  var ws = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  var data = ws.getValues();
  
  var announcementCol = 1,    // "constants" for column numbers
      dateCol = 2;
  var results = [];           // Array to hold announcements for today

  if (data.length > 1) {
    // Loop over rows that contain announcements
    for (var row = 1; row < data.length; row++) {
      // Check that today is a date that the announcement should be included.
      if(new Date(data[row][1]) <= new Date() && new Date(data[row][2]) >= new Date() ) {
        // Is there an announcement in this row?
        if(data[row][announcementCol]!=""){
          // Add this announcement to results
          results.push(data[row][announcementCol]);
        }
      }
    }
  }
  
  // Show results in log
  Logger.log( JSON.stringify( results ) );
}

Same message repeating 3 times

1 Answers1

Updated code