OrthoDex/Code.gs

## Code.gs

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Copy from Main Sheet', functionName: 'loadHTML'}
  ];
  spreadsheet.addMenu('Get Data', menuItems);
}

function loadHTML() {
  // Prompt the user for a city and date.
  var html = HtmlService.createHtmlOutputFromFile('date_select')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'Data Extraction Form');
}
/** Read the Master Sheet, if it is the correct city, copy the rows
 * into Copy Sheet
 */
function getNamedMonth(no)
{
  var month = new Array();
  month[0] = "Jan";
  month[1] = "Feb";
  month[2] = "Mar";
  month[3] = "Apr";
  month[4] = "May";
  month[5] = "June";
  month[6] = "July";
  month[7] = "Aug";
  month[8] = "Sep";
  month[9] = "Oct";
  month[10] = "Nov";
  month[11] = "Dec";
  return month[no];
}

function getFormattedDate(dateString)
{
  var dateArray = dateString.split("-",3);

  var year = dateArray[0];
  var month = dateArray[1];
  var day = dateArray[2];
  var date = new Date(year, month - 1, day);

  return date;
}

function processForm(formObject) {
  var city = formObject.city;
  var date = formObject.date;
  Logger.log("Date:"+date);
  copyFromMaster(city, date);
      }

function copyFromMaster(city, selectedDate) {
  var spreadsheet = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.openById("Enter Spreadsheet ID");
  var sourcesheet = source.getSheetByName('Enter Sheet name');

  var currDate = getFormattedDate(selectedDate);
  Logger.log("Current Date = " + currDate + " Selected Date = " + selectedDate);

  var name = currDate.getDate() + " " + getNamedMonth(currDate.getMonth());
  var targetSheet = spreadsheet.getSheetByName(name);

  var ui = SpreadsheetApp.getUi();
  if (targetSheet) {
    var result = ui.alert(
     'There already exists a sheet for that date',
      ui.ButtonSet.OK);
    if (result == ui.Button.OK)
    {
      targetSheet.activate();
      return;
    }
  } else {
    targetSheet =
        SpreadsheetApp.getActive().insertSheet(name);
  }

  var sheetTitle = Utilities.formatString('Main Data Title for' + city + ' on ' + name);
  var headers = [
    [sheetTitle, '','','','',''],
    ['Col1', 'Col2', 'Col3', 'Col4 ', 'Col5', 'Col6']
  ];
  targetSheet.getRange(1, 1, headers.length, 6).setValues(headers);
  targetSheet.getRange('A1:F1').merge();
  targetSheet.getRange('A2:F2').setFontWeight('bold');

  var range = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
  var rangeValues = range.getValues();
  var cityValues = [];
  var checkDate = Utilities.formatDate(currDate,"IST","dd/MM/YYYY");

  //Scan entire spreadsheet
  for(var i = 0; i < range.getNumRows(); i++) {
    temp = Utilities.formatDate(new Date(rangeValues[i][0]), source.getSpreadsheetTimeZone(), "dd/MM/YYYY");
    //cmp = compareDate(temp, currDate);
    if(rangeValues[i][4] == String(city) && temp == checkDate )
    {
      Logger.log("FOUND: range" + i + temp+ "=" + checkDate + "?");
      rangeValues[i][0] = Utilities.formatDate(new Date(rangeValues[i][0]), source.getSpreadsheetTimeZone(), "dd/MM/YYYY HH:mm:ss");
      Logger.log( "TEMP = " + temp + "Rangevalues = " + rangeValues[i][0] + " " + rangeValues[i][1]);
      cityValues.push(rangeValues[i]);
    }
  }

  if(cityValues.length == 0)
  {
    var result = ui.alert(
     'No Data for today!',
      ui.ButtonSet.OK);
    spreadsheet.deleteSheet(targetSheet);
    return;
  }

  var targetrange = targetSheet.getRange(headers.length + 1, 1, cityValues.length, 6);
  targetrange.setValues(cityValues);

  SpreadsheetApp.flush();
}

## date_select.html
<!DOCTYPE html>
<html>
  <head>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <base target="_parent" />
  </head>
  <script>
  function handleFormSubmit(formObject) {
        google.script.run.processForm(formObject);
        google.script.host.close();
      }
  </script>
  <body>
    <div class="block form-group">
    <form id="myForm" onsubmit="handleFormSubmit(this)">
    <label for="select">Select City</label>
     <select id="select" name="city">
      <option value="Mumbai">Mumbai</option>
      <option value="Delhi">Delhi</option>
      <option value="Chennai">Chennai</option>
      <option value="Bangalore">Bangalore</option>
     </select>
     <br>
     <label for="date">Set Date</label>
     <input id="date" name="date" type="date" />
     <br>
     <input type="submit" value="Submit" />
    </form>
    </div>
  </body>
</html>

	/**
	* A special function that runs when the spreadsheet is open, used to add a
	* custom menu to the spreadsheet.
	*/
	function onOpen() {
	var spreadsheet = SpreadsheetApp.getActive();
	var menuItems = [
	{name: 'Copy from Main Sheet', functionName: 'loadHTML'}
	];
	spreadsheet.addMenu('Get Data', menuItems);
	}

	function loadHTML() {
	// Prompt the user for a city and date.
	var html = HtmlService.createHtmlOutputFromFile('date_select')
	.setSandboxMode(HtmlService.SandboxMode.IFRAME);
	SpreadsheetApp.getUi()
	.showModalDialog(html, 'Data Extraction Form');
	}
	/** Read the Master Sheet, if it is the correct city, copy the rows
	* into Copy Sheet
	*/
	function getNamedMonth(no)
	{
	var month = new Array();
	month[0] = "Jan";
	month[1] = "Feb";
	month[2] = "Mar";
	month[3] = "Apr";
	month[4] = "May";
	month[5] = "June";
	month[6] = "July";
	month[7] = "Aug";
	month[8] = "Sep";
	month[9] = "Oct";
	month[10] = "Nov";
	month[11] = "Dec";
	return month[no];
	}

	function getFormattedDate(dateString)
	{
	var dateArray = dateString.split("-",3);

	var year = dateArray[0];
	var month = dateArray[1];
	var day = dateArray[2];
	var date = new Date(year, month - 1, day);

	return date;
	}

	function processForm(formObject) {
	var city = formObject.city;
	var date = formObject.date;
	Logger.log("Date:"+date);
	copyFromMaster(city, date);
	}

	function copyFromMaster(city, selectedDate) {
	var spreadsheet = SpreadsheetApp.getActive();
	var source = SpreadsheetApp.openById("Enter Spreadsheet ID");
	var sourcesheet = source.getSheetByName('Enter Sheet name');

	var currDate = getFormattedDate(selectedDate);
	Logger.log("Current Date = " + currDate + " Selected Date = " + selectedDate);

	var name = currDate.getDate() + " " + getNamedMonth(currDate.getMonth());
	var targetSheet = spreadsheet.getSheetByName(name);

	var ui = SpreadsheetApp.getUi();
	if (targetSheet) {
	var result = ui.alert(
	'There already exists a sheet for that date',
	ui.ButtonSet.OK);
	if (result == ui.Button.OK)
	{
	targetSheet.activate();
	return;
	}
	} else {
	targetSheet =
	SpreadsheetApp.getActive().insertSheet(name);
	}

	var sheetTitle = Utilities.formatString('Main Data Title for' + city + ' on ' + name);
	var headers = [
	[sheetTitle, '','','','',''],
	['Col1', 'Col2', 'Col3', 'Col4 ', 'Col5', 'Col6']
	];
	targetSheet.getRange(1, 1, headers.length, 6).setValues(headers);
	targetSheet.getRange('A1:F1').merge();
	targetSheet.getRange('A2:F2').setFontWeight('bold');

	var range = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
	var rangeValues = range.getValues();
	var cityValues = [];
	var checkDate = Utilities.formatDate(currDate,"IST","dd/MM/YYYY");

	//Scan entire spreadsheet
	for(var i = 0; i < range.getNumRows(); i++) {
	temp = Utilities.formatDate(new Date(rangeValues[i][0]), source.getSpreadsheetTimeZone(), "dd/MM/YYYY");
	//cmp = compareDate(temp, currDate);
	if(rangeValues[i][4] == String(city) && temp == checkDate )
	{
	Logger.log("FOUND: range" + i + temp+ "=" + checkDate + "?");
	rangeValues[i][0] = Utilities.formatDate(new Date(rangeValues[i][0]), source.getSpreadsheetTimeZone(), "dd/MM/YYYY HH:mm:ss");
	Logger.log( "TEMP = " + temp + "Rangevalues = " + rangeValues[i][0] + " " + rangeValues[i][1]);
	cityValues.push(rangeValues[i]);
	}
	}

	if(cityValues.length == 0)
	{
	var result = ui.alert(
	'No Data for today!',
	ui.ButtonSet.OK);
	spreadsheet.deleteSheet(targetSheet);
	return;
	}

	var targetrange = targetSheet.getRange(headers.length + 1, 1, cityValues.length, 6);
	targetrange.setValues(cityValues);

	SpreadsheetApp.flush();
	}
	<!DOCTYPE html>
	<html>
	<head>
	<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
	<base target="_parent" />
	</head>
	<script>
	function handleFormSubmit(formObject) {
	google.script.run.processForm(formObject);
	google.script.host.close();
	}
	</script>
	<body>
	<div class="block form-group">
	<form id="myForm" onsubmit="handleFormSubmit(this)">
	<label for="select">Select City</label>
	<select id="select" name="city">
	<option value="Mumbai">Mumbai</option>
	<option value="Delhi">Delhi</option>
	<option value="Chennai">Chennai</option>
	<option value="Bangalore">Bangalore</option>
	</select>
	<br>
	<label for="date">Set Date</label>
	<input id="date" name="date" type="date" />
	<br>
	<input type="submit" value="Submit" />
	</form>
	</div>
	</body>
	</html>