talesa/fill_spreadsheet.html

## fill_spreadsheet.html
<!DOCTYPE html>
<html>
  <head>
    <title>Google Sheets API Quickstart</title>
    <meta charset='utf-8' />

    <!-- <link rel="stylesheet" type="text/css" href="./mystyle.css"> -->
  </head>
  <body>
    <p>Google Sheets API Quickstart</p>

    <!--Add buttons to initiate auth sequence and sign out-->
    <button id="authorize-button" style="display: none;">Authorize</button>
    <button id="signout-button" style="display: none;">Sign Out</button>

    <div id="map"></div>

    <pre id="content"></pre>

    <script type="text/javascript">
      let map;
      let service;

      let adresy_cache = {};

      // Client ID and API key from the Developer Console
      let CLIENT_ID = '';

      // Array of API discovery doc URLs for APIs used by the quickstart
      let DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];

      // Authorization scopes required by the API; multiple scopes can be
      // included, separated by spaces.
      let SCOPES = "https://www.googleapis.com/auth/spreadsheets";

      let authorizeButton = document.getElementById('authorize-button');
      let signoutButton = document.getElementById('signout-button');

      /**
      *  On load, called to load the auth2 library and API client library.
      */
      function handleClientLoad() {
        gapi.load('client:auth2', initClient);
      }

      /**
      *  Initializes the API client library and sets up sign-in state
      *  listeners.
      */
      function initClient() {
        gapi.client.init({
          discoveryDocs: DISCOVERY_DOCS,
          clientId: CLIENT_ID,
          scope: SCOPES
        }).then(function () {
          // Listen for sign-in state changes.
          gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

          // Handle the initial sign-in state.
          updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
          authorizeButton.onclick = handleAuthClick;
          signoutButton.onclick = handleSignoutClick;
        });
      }

      /**
      *  Called when the signed in status changes, to update the UI
      *  appropriately. After a sign-in, the API is called.
      */
      function updateSigninStatus(isSignedIn) {
        if (isSignedIn) {
          authorizeButton.style.display = 'none';
          signoutButton.style.display = 'block';
          listMajors();
        } else {
          authorizeButton.style.display = 'block';
          signoutButton.style.display = 'none';
        }
      }

      /**
      *  Sign in the user upon button click.
      */
      function handleAuthClick(event) {
        gapi.auth2.getAuthInstance().signIn();
      }

      /**
      *  Sign out the user upon button click.
      */
      function handleSignoutClick(event) {
        gapi.auth2.getAuthInstance().signOut();
      }

      /**
      * Append a pre element to the body containing the given message
      * as its text node. Used to display the results of the API call.
      *
      * @param {string} message Text to be placed in pre element.
      */
      function appendPre(message) {
        let pre = document.getElementById('content');
        let textContent = document.createTextNode(message + '\n');
        pre.appendChild(textContent);
      }

      function listMajors() {
        gapi.client.sheets.spreadsheets.values.get({
          spreadsheetId: '',
          range: 'szkoly_adresy!A1:A939',
        }).then(function(response) {
          let range = response.result;
          let addresses = Array(range.values.length);
          if (range.values.length > 0) {
          	let p = Promise.resolve();
            for (let i = 0; i < range.values.length; i++) {
              let row = range.values[i];
              let queryString = row[0];
              console.log(queryString);
              p = p.then(() => {
                return putAddressInArray(addresses, queryString, i)
                	.catch( (err) => console.log(err));
              });
          	}
            p.then(() => {
            	console.log('szkoly_adresy!B1:B' + String(range.values.length));

            	return gapi.client.sheets.spreadsheets.values.update({
              	spreadsheetId: '',
	              range: 'szkoly_adresy!B1:B' + String(range.values.length),
  	            valueInputOption: 'USER_ENTERED',
    	          values: addresses,
            	}).then((response) => console.log(response));
          	}).catch((err) => console.log(err));
          } else {
            appendPre('No data found.');
          }
        }, function(response) {
          appendPre('Error: ' + response.result.error.message);
        });
      }

      function initMap() {
        let centre = {lat: 52.2297, lng: 19.0122};

        map = new google.maps.Map(document.getElementById('map'), {
          center: centre,
          zoom: 6
        });

        service = new google.maps.places.PlacesService(map);
      }


			function textSearch(queryString) {
       	return new Promise((resolve, reject) => {
          service.textSearch({query: queryString},
            function(response, status) {
              if (status === google.maps.places.PlacesServiceStatus.OK)
                resolve(response)
              else
                reject(response);
            });
        });
      }

			function fetchAddress(queryString) {
          if(!(queryString in adresy_cache)) {
            return textSearch(queryString).then((response) => {
              adresy_cache[queryString] = response[0].formatted_address;
              return Promise.resolve(adresy_cache[queryString]);
            });
          } else {
            return Promise.resolve(adresy_cache[queryString]);
          }
      }

			function putAddressInArray(addresses, queryString, i) {
        console.log(queryString)
        return fetchAddress(queryString).then((address) => {
          addresses[i] = [address];
          return Promise.resolve();
        });
      }
    </script>

    <script src="https://maps.googleapis.com/maps/api/js?key=AIzaSyBsqvQT-anzO5QGEi-ZhHZD4zt7vVvmHvc&libraries=places&callback=initMap" async defer>
    </script>
    <script async defer src="https://apis.google.com/js/api.js"
      onload="this.onload=function(){};handleClientLoad()"
      onreadystatechange="if (this.readyState === 'complete') this.onload()">
    </script>
  </body>
</html>
	<!DOCTYPE html>
	<html>
	<head>
	<title>Google Sheets API Quickstart</title>
	<meta charset='utf-8' />

	<!-- <link rel="stylesheet" type="text/css" href="./mystyle.css"> -->
	</head>
	<body>
	<p>Google Sheets API Quickstart</p>

	<!--Add buttons to initiate auth sequence and sign out-->
	<button id="authorize-button" style="display: none;">Authorize</button>
	<button id="signout-button" style="display: none;">Sign Out</button>

	<div id="map"></div>

	<pre id="content"></pre>

	<script type="text/javascript">
	let map;
	let service;

	let adresy_cache = {};

	// Client ID and API key from the Developer Console
	let CLIENT_ID = '';

	// Array of API discovery doc URLs for APIs used by the quickstart
	let DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];

	// Authorization scopes required by the API; multiple scopes can be
	// included, separated by spaces.
	let SCOPES = "https://www.googleapis.com/auth/spreadsheets";

	let authorizeButton = document.getElementById('authorize-button');
	let signoutButton = document.getElementById('signout-button');

	/**
	* On load, called to load the auth2 library and API client library.
	*/
	function handleClientLoad() {
	gapi.load('client:auth2', initClient);
	}

	/**
	* Initializes the API client library and sets up sign-in state
	* listeners.
	*/
	function initClient() {
	gapi.client.init({
	discoveryDocs: DISCOVERY_DOCS,
	clientId: CLIENT_ID,
	scope: SCOPES
	}).then(function () {
	// Listen for sign-in state changes.
	gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

	// Handle the initial sign-in state.
	updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
	authorizeButton.onclick = handleAuthClick;
	signoutButton.onclick = handleSignoutClick;
	});
	}

	/**
	* Called when the signed in status changes, to update the UI
	* appropriately. After a sign-in, the API is called.
	*/
	function updateSigninStatus(isSignedIn) {
	if (isSignedIn) {
	authorizeButton.style.display = 'none';
	signoutButton.style.display = 'block';
	listMajors();
	} else {
	authorizeButton.style.display = 'block';
	signoutButton.style.display = 'none';
	}
	}

	/**
	* Sign in the user upon button click.
	*/
	function handleAuthClick(event) {
	gapi.auth2.getAuthInstance().signIn();
	}

	/**
	* Sign out the user upon button click.
	*/
	function handleSignoutClick(event) {
	gapi.auth2.getAuthInstance().signOut();
	}

	/**
	* Append a pre element to the body containing the given message
	* as its text node. Used to display the results of the API call.
	*
	* @param {string} message Text to be placed in pre element.
	*/
	function appendPre(message) {
	let pre = document.getElementById('content');
	let textContent = document.createTextNode(message + '\n');
	pre.appendChild(textContent);
	}

	function listMajors() {
	gapi.client.sheets.spreadsheets.values.get({
	spreadsheetId: '',
	range: 'szkoly_adresy!A1:A939',
	}).then(function(response) {
	let range = response.result;
	let addresses = Array(range.values.length);
	if (range.values.length > 0) {
	let p = Promise.resolve();
	for (let i = 0; i < range.values.length; i++) {
	let row = range.values[i];
	let queryString = row[0];
	console.log(queryString);
	p = p.then(() => {
	return putAddressInArray(addresses, queryString, i)
	.catch( (err) => console.log(err));
	});
	}
	p.then(() => {
	console.log('szkoly_adresy!B1:B' + String(range.values.length));

	return gapi.client.sheets.spreadsheets.values.update({
	spreadsheetId: '',
	range: 'szkoly_adresy!B1:B' + String(range.values.length),
	valueInputOption: 'USER_ENTERED',
	values: addresses,
	}).then((response) => console.log(response));
	}).catch((err) => console.log(err));
	} else {
	appendPre('No data found.');
	}
	}, function(response) {
	appendPre('Error: ' + response.result.error.message);
	});
	}

	function initMap() {
	let centre = {lat: 52.2297, lng: 19.0122};

	map = new google.maps.Map(document.getElementById('map'), {
	center: centre,
	zoom: 6
	});

	service = new google.maps.places.PlacesService(map);
	}


	function textSearch(queryString) {
	return new Promise((resolve, reject) => {
	service.textSearch({query: queryString},
	function(response, status) {
	if (status === google.maps.places.PlacesServiceStatus.OK)
	resolve(response)
	else
	reject(response);
	});
	});
	}

	function fetchAddress(queryString) {
	if(!(queryString in adresy_cache)) {
	return textSearch(queryString).then((response) => {
	adresy_cache[queryString] = response[0].formatted_address;
	return Promise.resolve(adresy_cache[queryString]);
	});
	} else {
	return Promise.resolve(adresy_cache[queryString]);
	}
	}

	function putAddressInArray(addresses, queryString, i) {
	console.log(queryString)
	return fetchAddress(queryString).then((address) => {
	addresses[i] = [address];
	return Promise.resolve();
	});
	}
	</script>

	<script src="https://maps.googleapis.com/maps/api/js?key=AIzaSyBsqvQT-anzO5QGEi-ZhHZD4zt7vVvmHvc&libraries=places&callback=initMap" async defer>
	</script>
	<script async defer src="https://apis.google.com/js/api.js"
	onload="this.onload=function(){};handleClientLoad()"
	onreadystatechange="if (this.readyState === 'complete') this.onload()">
	</script>
	</body>
	</html>