How to combine the Global variables with a function?

Question

Could someone please help make the global variables not global anymore. Function checkDate() needs to be able to access (var = formatteddate). I am not able to combine the global variables with Function checkDate() as it just keeps repeating the ui.prompt over and over again.

A little background on the script, a date gets inputted through a ui.prompt, the year is taken from that date, and placed as (var = formatteddate) in number format.

Function checkDate(), checks all the rows for the year (var = formatteddate) in column 7.

Function filterRows(), takes the information from checkdate and filters the rows.

The script duplicating and deleting the sheet is just part of my testing so I don't need to always duplicate my data before testing the script.

If you are to test the script, please create two sheets, 'Master Log' and 'Copy of Master Log'. In 'Master Log' Starting from row 2, place some dates down column H (column 7). Ideally some 2016 and 2017 dates. To see the filtering in action. 'Copy of Master Log' will be deleted/created on each run.

My ideal solution would be to combine the GLOBAL lines with Function checkDate().

Thank you! =)

var ui = SpreadsheetApp.getUi();
var prompt = ui.prompt('Start Date', 'Enter a date in m/d/y format', ui.ButtonSet.OK_CANCEL);
var date = new Date(prompt.getResponseText());
var formatteddate = Number(Utilities.formatDate(date, "PST", "yyyy"));
Logger.log(date);
Logger.log(formatteddate);

function checkDate(row) {

  return (row[7].getFullYear() === formatteddate);  // Check column B
  Logger.log(row[7].getFullYear() === formatteddate)
  }

function filterRows() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = Spreadsheet.getSheetByName('Copy of Master Log');
  var sheet2 = Spreadsheet.getSheetByName('Master Log');

  Spreadsheet.deleteSheet(sheet1);
  Spreadsheet.setActiveSheet(sheet2);
  Spreadsheet.duplicateActiveSheet();

  var headers = 1; // # rows to skip
  var sheet = Spreadsheet.getSheetByName('Copy of Master Log');
  var data = sheet.getDataRange().getValues();
  var headerData = data.splice(0,headers); // Skip header rows
  var filteredData = data.filter( checkDate );
  var outputData = headerData.concat(filteredData);  // Put headers back
  Logger.log(filteredData)

  sheet.clearContents();  // Clear content, keep format

  // Save filtered values
  sheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData); 
}

Seems to me row[7].getFullYear() === formatteddate is comparing a number to a string, are you sure that's correct? — RobG

RobG RobG · Accepted Answer · 2017-02-04T00:54:55

Seems to me that formatteddate (which is really just a year) should be part of filterRows. There's no need for checkDate as it doesn't seem particularly reusable.

Consider:

function filterRows() {

  // Do the prompt when necessary
  var prompt = ui.prompt('Start Date', 'Enter a date in m/d/y format', ui.ButtonSet.OK_CANCEL);
  // Don't convert to Date, just get the year
  var year = prompt.getResponseText().split(/\//)[2];

  ...

  // Do comparison, use function expression
  var filteredData = data.filter(function(row) {
    return row[7].getFullYear() == year; // Allow compare string to number
  });

  ...
}

How to combine the Global variables with a function?

1 Answers