Google Sheets and Trouble with oldValue in Edit Event Objects

Simple and Installable triggers in Google Apps Script on sheets is a very useful feature to certain event occurs in a sheet. Events such onChange, onSelectionChange and onEdit instance. On your script if manage properly, it could be a great extension to your user interface. Let's say a user selects a certain cell in sheets, there's a corresponding script that would prompt a user dialog box, where further operations are presented to the user. Or, if a user edit's a value that would trigger another script do some customized function.

oldValue

These triggers are really useful. But, there's one annoying feature which that unexpectedly not available in sheets. Let's say, Cell A1 has a numerical value of 4. Then you edited Cell A1 to change value to 5. In the onEdit(e), if you call the function e.oldValue, you get 4. This is totally fine just how'd you expect it to be. What if from value of 4 in Cell A1, you deleted it. So basically, it is now blank. If you call the range("A1").oldValue. What would you think is the return value?...(blank) or null?...Actually it's undefined.

Does it even makes sense?

It doesn't and that's a problem. We are supposed to capture every value, if they have that oldValue method in the first place. So, is there a solution? Actually there is. But, to implement this, we need to add a mirror tab or sheet of the target sheet being edited. Let's examine the implementation flow. 

It's a very simple solution, by the way. Every Edit, we need to write the same value, to a same range in the mirror tab. But, retrieving oldValue if we are deleting a cell value, we need to retrieve firs the mirror value before writing the new value which is now blank.

Demo - Let's do above scenario

1. On "Main" tab and Range("A1"), write the number 4. In doing so, the script write's the same number in "Main_Mirror" tab.
2. Now, let's change the value in Range("A1") to 5. We have below in the Log. 
   
3. So now, let us delete 5 from Range("A1") to 5. We should expect NEWVALUE as empty or blank(""). While, OLDVALUE is now 5.

There you have it. It's a simple and quick solution. Please see the actual script below. Comment below and subscribe to my for more Google Apps Script tricks.

The script (Please click LINK if the script text doesn't show up):

	var ss = SpreadsheetApp.getActive();
	var wsMain = ss.getSheetByName('Main');
	var wsMainMirror = ss.getSheetByName('Main_Mirror');
	function onEdit(e){
	var rng = e.range;
	var wsName = rng.getSheet().getName();
	var wsCol = rng.getColumn();
	var OLDVALUE;
	var NEWVALUE;
	if(wsName === 'Main'){
	if(rng.getValue() != ""){
	OLDVALUE = e.oldValue;
	}else{
	OLDVALUE = wsMainMirror.getRange(rng.getA1Notation()).getValue();
	}
	NEWVALUE = rng.getValue();
	wsMainMirror.getRange(rng.getA1Notation()).setValue(NEWVALUE);
	Logger.log("NEWVALUE : " + NEWVALUE);
	Logger.log("OLDVALUE : " + OLDVALUE);
	}
	}

view raw oldValue1.js hosted with ❤ by GitHub

Google Apps Script Attendance Log Application

Here's another quick project that utilizes simple scripts. We are going to create a simple attendance logger application written in Google Apps Script. It's a cool project you can replicate instead of just using a conventional Google Form. Although, in this demo, you could say a Google Form is the better approach. But, just want you to know the basic steps in creating a function web application that uses Google Sheet as a database.

I've posted an article before, with basically, same approach. That was more of pulling from a database. Now we are going to do writing to a database. This is going to be interesting, since after this you both have the read and write flow with Google Sheet as a database. Also, if you need to learn Google Apps Script further, click Buy Now button below for the book. Available in Amazon.

Now, into the script First, let's picture out the overall process. It has two components;

1. The form - this is a web application. The HTML code and the apps script that will handle the interface between HTML and Google Sheets. With the interface, I'm introducing an alert if the log function was successful or not. You will see below script a google.script.run function for handling this interface. Plain HTML javascript cannot communicate with Google Apps Script. In other words we cannot log to Google sheets directly. Unless we are using an API. That is of another discussion. You will see also, that I've separated the javascript component. I'm introducing it this so you could see before hand the advantage in doing it this way. Instead of writing it together with the HTML file. The good thing about this approach, is that, you can modularize your code. Very helpful when your code grows. It's not so good with comes to debugging if it's integrated directly with the HTML component.
2. The Database - this is the Google Sheet where we log the attendance. No need to discuss further on this aspect. But, point to note though, when you pass a date time format from javascript to app script needs attention. You need some data type conversion before you write them into the database.

The Script:

	/**
	THIS WILL BE THE .GS COMPONENT
	*/
	function doGet(request) {
	return HtmlService
	.createTemplateFromFile('HTML')
	.evaluate()
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.setTitle('ATTENDANCE APP');
	}
	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.getContent();
	}
	function writeToDb(data){
	var ss = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');//<---change the sheet id here
	var ws = ss.getSheetByName('Attendance_Log');//<--- you may create a sheet with same name
	ws.appendRow(data);
	}
	function test_write(){
	writeToDb(['today','me','you','@']);
	}

view raw attendace_log_gs_v0.js hosted with ❤ by GitHub

	<!DOCTYPE html>
	<?!= include("SCRIPT"); ?>
	<html>
	<head>
	<base target="_top">
	</head>
	<body>
	<div class="container">
	<label for="fname"><b>First Name</b></label>
	<input type="text" placeholder="Enter First Name" name="fname" id="fname" required>
	<label for="lname"><b>Password</b></label>
	<input type="text" placeholder="Enter Last Name" name="lname" id="lname" required>
	<label for="email"><b>Password</b></label>
	<input type="text" placeholder="Enter Email" name="email" id="email" required>
	<button onclick="log_attendance()">Log</button>
	</div>
	</body>
	</html>

view raw attendace_log_html_v0.html hosted with ❤ by GitHub

	/**
	THIS WILL BE THE HTML SCRIPT COMPONENT
	*/
	<script>
	function log_attendance(){
	var time = new Date();
	var fname = document.getElementById('fname').value;
	var lname = document.getElementById('lname').value;
	var email = document.getElementById('email').value;
	google.script.run.withFailureHandler(logFailed).withSuccessHandler(clearFields).writeToDb([time.toString(),fname,lname,email]);
	}
	function clearFields(){
	document.getElementById('fname').value = ""
	document.getElementById('lname').value = ""
	document.getElementById('email').value = ""
	alert("Log Successful!");
	}
	function logFailed(){
	alert("Log Failed!");
	}
	</script>

view raw attendace_log_js_v0.js hosted with ❤ by GitHub

How to scrape for email contacts

Polls might tell you, that, VBA is a dying language. According to PYPL index, Python has the top share with a +3.9% trend. While, VBA is at 14 with 0% movement. But, still in the top 20 beating Ruby at #15. With that said let's do a simple quick project that will prove VBA is still powerful or useful in some aspect.

Web scraping

We have a preferred language and I know, for some, VBA is not one of them. Let's pause for a second and admit that VBA is still potent with web scraping. Although, it still based on a internet explorer library. But, I should say it still perfect for tricking robot.txt to some website in dealing with automated crawling. 

The Goal

We need to do a script that will scrape email addresses. Yes, it's a goldmine for the email marketer. I will attempt to scrape publicly shown contact information. For this demo, I will scrape Students or potentially school staffs email address. The only drawback with all web scraping techniques is the maximum number of attempts allowed for some website. Still relate's to the robot.txt setup. But, you can always delay between calls. If you do bulk fetch. Here are the steps and let me know in the comment section below for your thoughts.

1. Let's do https://directory.utexas.edu/index.php? It's University of Texas directory
2. Let's try to fetch contact details for top used names "James". It will return all names with it's corresponding url to individual info page
3. Scrape the email address and other relevant info

The Script:

	Sub GETCONTACTINFO(href As String, ws As Worksheet)
	'This will scrape the individual contact infos from URL
	'THIS WILL EXTRACT THE ACTUAL CONTACT INFORMAT FROM THE INDIVIDUAL INFO PAGE
	Dim irow As Integer, colCount As Integer
	Dim IE As New SHDocVw.InternetExplorer
	Dim HTMLDoc As MSHTML.HTMLDocument
	Dim HTMLDocElements As MSHTML.IHTMLElementCollection
	Dim HTMLDocElement As MSHTML.IHTMLElement
	'THIS IS THE INITIAL URL CRAWL OF THE MAIN PRODUCT PAGE
	IE.navigate "https://directory.utexas.edu/index.php?q=James&scope=student&i=46"
	IE.Visible = True
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading page main project page..."
	Loop
	Set HTMLDoc = IE.document
	'THIS IS THE TARGET HTML TAG
	Set HTMLDocElements = HTMLDoc.getElementsByTagName("tr")
	If HTMLDocElements.Length <> 0 Then
	colCount = 1
	irow = Cells(Rows.Count, 1).End(xlUp).Row + 1
	'ITERATE EACH HTML ELEMENTS COLLECTED
	For Each HTMLDocElement In HTMLDocElements
	Dim innerText As String
	Dim innerTextSplit As Variant
	innerText = HTMLDocElement.innerText
	innerTextSplit = Split(innerText, ":")
	'WRITE THE URL'S
	ws.Cells(irow, colCount).Value = innerTextSplit(UBound(innerTextSplit))
	colCount = colCount + 1
	Next HTMLDocElement
	End If
	IE.Quit
	End Sub

view raw utexasdir_scraping_getinfo_v0.vb hosted with ❤ by GitHub

	Sub GETALLCONTACTINFOS()
	'This will scrape in bulk all contact URL's fetched
	'THIS WILL DO BULK FETCH
	Dim ws As Worksheet, ws1 As Worksheet
	Dim urlRng As Range, rng As Range
	Set ws = Sheets("utexas students dir")
	Set urlRng = ws.Range("A2:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row + 1)
	Sheets.Add After:=ActiveSheet
	Set ws1 = ActiveSheet
	Application.ScreenUpdating = False
	For Each rng In urlRng
	GETCONTACTINFO rng.Value, ws1
	Next rng
	Application.ScreenUpdating = True
	End Sub

view raw utexasdir_scraping_getinfos_v0.vb hosted with ❤ by GitHub

	Sub GETINDIVIDUALCONTACTURLS()
	'This will extract all contact URL results
	'THIS WILL SCRAPE ALL CONTACT URLS FOR A SPECIFIC NAME
	'AND LIST ALL THOSE URLS IN A SHEET
	Dim href As String
	Dim targetName As String
	Dim ws As Worksheet
	Dim irow As Integer
	Dim IE As New SHDocVw.InternetExplorer
	Dim HTMLDoc As MSHTML.HTMLDocument
	Dim HTMLDocElements As MSHTML.IHTMLElementCollection
	Dim HTMLDocElement As MSHTML.IHTMLElement
	Set ws = Sheets("utexas students dir") '<--- you can create another tab to where we will write the fetched URLs
	targetName = "James" '<--- you can change target name here
	href = "https://directory.utexas.edu/index.php?q=" & targetName & "&scope=student&submit=Search"
	'THIS IS THE INITIAL URL CRAWL OF THE MAIN PRODUCT PAGE
	IE.navigate href
	IE.Visible = False
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading page main project page..."
	Loop
	Set HTMLDoc = IE.document
	'THIS IS THE TARGET HTML TAG
	Set HTMLDocElements = HTMLDoc.getElementsByTagName("a")
	If HTMLDocElements.Length <> 0 Then
	'ITERATE EACH HTML ELEMENTS COLLECTED
	For Each HTMLDocElement In HTMLDocElements
	'FILTER OUT THE NON TARGET a TAG
	If HTMLDocElement.innerText <> "About the Directory " _
	And HTMLDocElement.innerText <> "Frequently Asked Questions" _
	And HTMLDocElement.innerText <> "University Offices" _
	And HTMLDocElement.innerText <> "UT System Administration Directory" _
	And HTMLDocElement.innerText <> "Advanced Search" _
	And HTMLDocElement.innerText <> "UT Austin Home" _
	And HTMLDocElement.innerText <> "Emergency Information" _
	And HTMLDocElement.innerText <> "Site Policies" _
	And HTMLDocElement.innerText <> "Web Accessibility Policy" _
	And HTMLDocElement.innerText <> "Web Privacy Policy" _
	And HTMLDocElement.innerText <> "Adobe Reader" _
	And HTMLDocElement.innerText <> "Skip to main content" _
	And HTMLDocElement.className <> "logo" Then
	If HTMLDocElement.innerText <> "" Then
	'DETERMINE LAST NON-EMPTY ROW IN A COLUMN
	irow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
	'WRITE THE URL'S
	ws.Cells(irow, 1).Value = HTMLDocElement.getAttribute("href")
	End If
	End If
	Next HTMLDocElement
	End If
	IE.Quit
	End Sub

view raw utexasdir_scraping_v0.vb hosted with ❤ by GitHub

That's it, this is just very foundational. You can tweak this script a little bit to fit your requirements. To learn more about VBA, click Buy Now button for the book. Now available in Amazon.

How to Edit Google Apps Scripts in you iPad

You may have probably tried this and never succeed. Everyone is mobile and you don't have with always a laptop. How portable it is. A laptop is still a laptop. A PC with flip covers that you need to carry around with large bag. For developers, you'll never know when a solution pops out from script bug. Or, when a great idea lights and you need to get right away and write codes. But, you're stuck with just a mobile device. An phone or an iPad perhaps.

Google Apps Script is a very scalable programming environment. As long as you have internet connection you are good to go. So, how do you edit a script in Google Apps Script with your iPad then?

The key is bookmarking. It's always a practice of mine to set aside a script file for the purpose of quick code writing. This step will be just for initial setup. You need to use your PC for this purpose.

1. Go to your Google Drive.
2. Click New>More>Google Apps Script - if missing, click "+ Connect more apps" and select from the list.
3. Once created, bookmark the URL. 
4. Send that URL to yourself. Or, create an online spreadsheet to log those URLs.

Now you have easy links to your Google Apps Script files. You can open it using your Chrome browser if your had it install in you iPad. Or, with Safari is fine as well.

The question is, do you have an iPad in the first place? Click Buy Now button below and take advantage of the full experience of Apple's flagship product. The latest iPad Pro. Enjoy it the the Magic Keyboard and your Google Apps Script coding is never been this portable.

What is my IP address

A very old question. Yet, always new. Here's a very interesting fact. See below charts searches involving this questions. Showing the top 5 searches and still a relevant question. Most of us are interfacing with a computer and we sometimes get's into point of fixing IT issues. Like what is my IP address? Courtesy of Google Trends this is a very relatable demographics

There are various ways on how to locate this information depending on your OS as well. I don't want to give the tedious part of those numerous clicks. But, let me share you a quick and simple way to get that. Just a note though, this applicable with Windows 10(Sorry to Mac OS and Windows 7 if there's still any).

1. Press Ctrl + Shift + Esc. These keys are easy to find. They are just a single column in your keyboard. This will open your Task Manager. 
2. Go to Performance Tab and choose Network for the details. Besides real-time date on current network performance, this dialog box shows the currently assigned IPv4 and IPv6 address for that connection, which comes in handy when you're working on a local network.

Now, to non Windows 10, you can have the click, clicks steps on this article.

Google Apps Script Web App Sheets as Database

Building your own web app or website takes amount of your money. Although there some low cost web hosting platform out there, it isn't cheap either. You can build the page with default templates. The next thing you need to consider is the database.

Database don't come cheap either and it will cost your per usage. Or, you could choose from various package. But, then again, you need to anticipate your space usage over time.

Google Sheets as database

Sheets already has the basic feature of a database. Which is,structured tables and can be accessed through script. We are going to use Google Apps Script for this and let me show you an overall process on how will this work.

The 3 components

1. Login page - this is a web page. For demo we are using this as to where user will provide login credentials. 3 parts for this. When you create a new Apps Script, you will need to have Code.gs for Google Apps Scripts, HTML for the login page and SCRIPT file for the javascript. I'm showing below a working example.
   
   
   Login Page Scripts:

   	/**
   	LOGIN PAGE .GS COMPONENT
   	*/
   	function doGet(request) {
   	return HtmlService
   	.createTemplateFromFile('HTML')
   	.evaluate()
   	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
   	.setTitle('SUNNYSIDEUP');
   	}
   	function include(filename) {
   	return HtmlService.createHtmlOutputFromFile(filename)
   	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
   	.getContent();
   	}
   	function LogInAccepted(username,password){
   	var ss = SpreadsheetApp.openById("1U7sWHbli6npx4RyuwYpoNa1kZS_rzpY8AsDVVYB3av8");//<--SPREADSHEET ID
   	var ws = ss.getSheetByName("Users");
   	var ws_log = ss.getSheetByName("Login History");
   	var tbl = ws.getDataRange().getValues();
   	var user = ArrayLib.filterByValue(tbl, 4, username);//<--INSTALL ArrayLib library. app id is MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T
   	if(user.length === 0){
   	return {ACCEPTED:false,ERROR_MSG:"User not recognized"};
   	}else{
   	var email = user[0][2];
   	var isAdmin = user[0][3];
   	var pass = user[0][5];
   	var activated = user[0][6];
   	var avatar_link = user[0][7];
   	var userdata = {USERNAME:username,EMAIL:email,IMAGELINK:avatar_link,ISADMIN:isAdmin};
   	if(activated){
   	if(pass === password){
   	ws_log.appendRow([new Date,username,true,"Log-in Accepted"]);
   	return {ACCEPTED:true,ERROR_MSG:"Log-in Accepted",USERDATA:userdata};
   	}else{
   	ws_log.appendRow([new Date,username,false,"Incorrect Password"]);
   	return {ACCEPTED:false,ERROR_MSG:"Incorrect Password",USERDATA:userdata};
   	}
   	}else{
   	ws_log.appendRow([new Date,username,false,"Deactivated account"]);
   	return {ACCEPTED:false,ERROR_MSG:"Deactivated account",USERDATA:userdata};
   	}
   	}
   	}
   	function LogSession(){
   	}

   view raw Code_gs.js hosted with ❤ by GitHub

   	<!--LOGIN PAGE HTML-->
   	<!DOCTYPE html>
   	<?!= include("SCRIPT"); ?>
   	<html>
   	<head>
   	<base target="_top">
   	</head>
   	<body>
   	<div class="container">
   	<label for="uname"><b>Username</b></label>
   	<input type="text" placeholder="Enter Username" name="uname" id="uname" required>
   	<label for="psw"><b>Password</b></label>
   	<input type="password" placeholder="Enter Password" name="psw" id="psw" required>
   	<button onclick="submit_credential()">Login</button>
   	</div>
   	</body>
   	</html>

   view raw HTML.html hosted with ❤ by GitHub

   	<!--LOGIN PAGE HTML SCRIPT COMPONENT-->
   	<script>
   	var appLink = {DEV:"https://script.google.com/macros/s/AKfycbyLjLk-HFsgDAvpxoJQXXaJWmvfKg9XfZAoHt8D4A5-/dev",
   	EXEC:"https://script.google.com/macros/s/AKfycbw9H6Ca0qYkcSZncPjWPMlJ_Ik1rhK4tRiLrGlxA4wVZWioRkpk/exec"};
   	function submit_credential(){
   	var uname = document.getElementById("uname").value;
   	var psw = document.getElementById("psw").value;
   	google.script.run.withSuccessHandler(credential_submisstion).LogInAccepted(uname,psw);
   	}
   	function credential_submisstion(result){
   	var post_user_data = "?username=" + result.USERDATA.USERNAME + "&email=" + result.USERDATA.EMAIL + "&imgUrl=" + result.USERDATA.IMAGELINK;
   	var appUrl;
   	if(result.ACCEPTED){
   	if(result.USERDATA.ISADMIN){
   	appUrl = appLink.EXEC + post_user_data;
   	}else{
   	appUrl = appLink.DEV_USER + post_user_data;
   	}
   	window.open(appUrl, '_top');
   	}else{
   	alert(result.ERROR_MSG);
   	}
   	}
   	</script>

   view raw SCRIPT.html hosted with ❤ by GitHub
2. User Credential Database - this is the repository of the user credentials. It could also be some other database needed to validate user id/passwords or user sessions. Here's how the spreadsheet was structured for application. You can use the credentials shown to preview the demo web application. Click LINK for the login page
   
3. Web app - this could be the main web page and where the services are in a successful login
   
   
   The Script:

   	/**
   	Landing Page Code.gs
   	.gs component
   	*/
   	function doGet(request) {
   	var params = JSON.parse(JSON.stringify(request));
   	var uname = params.parameter.username;
   	var email = params.parameter.email;
   	var imgUrl = params.parameter.imgUrl;
   	var default_imgUrl = "https://www.pngfind.com/pngs/m/381-3819326_default-avatar-svg-png-icon-free-download-avatar.png";
   	var html = HtmlService.createTemplateFromFile('HTML').evaluate().getContent();
   	html = html.replace(default_imgUrl, imgUrl);
   	html = html.replace('<a id="user_name">User Name</a>','<a id="user_name">' + uname + '</a>');
   	html = html.replace('<a id="email">email</a>','<a id="email">' + email + '</a>');
   	return HtmlService.createHtmlOutput(html)
   	.setTitle('LANDING PAGE | ' + uname);
   	}
   	function include(filename) {
   	return HtmlService.createHtmlOutputFromFile(filename)
   	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
   	.getContent();
   	}
   	function LOGOUT(uname){
   	var ss = SpreadsheetApp.openById("1U7sWHbli6npx4RyuwYpoNa1kZS_rzpY8AsDVVYB3av8");
   	var ws_log = ss.getSheetByName("Login History");
   	ws_log.appendRow([new Date(),uname,false,"Logged out"]);
   	var login_urls = {DEV:"https://script.google.com/macros/s/AKfycbzRFrHJYuRmvwbb5dvHQZR9_MYGbCJFCPyXsvfvVMIR/dev",
   	EXEC:"https://script.google.com/macros/s/AKfycbw4Aal12GLsTNUqKldrbKqUKV_QCWMakTVniBr80OCsPNqkmtqk/exec"};
   	return login_urls.EXEC;
   	}

   view raw lp_CODE_gs.js hosted with ❤ by GitHub

   	<!--THIS WILL BE YOUR HTML COMPONENT ON YOU APPS SCRIPT-->
   	<!DOCTYPE html>
   	<?!= include("SCRIPT"); ?>
   	<html>
   	<head>
   	<base target="_top">
   	</head>
   	<body>
   	<div style="margin:auto;">
   	<p>
   	<img src="https://www.pngfind.com/pngs/m/381-3819326_default-avatar-svg-png-icon-free-download-avatar.png" class="w3-circle" alt="avatar" style="width:50px;height:50px">
   	</p>
   	<p><a id="user_name">User Name</a></p>
   	<p><a id="email">email</a></p>
   	<p><a href='javascript:' onclick='logout()'>logout</a></p>
   	</div>
   	</body>
   	</html>

   view raw lp_HTML.html hosted with ❤ by GitHub

   	/**
   	THIS WILL BE THE HTML SCRIPT COMPONENT
   	*/
   	<script>
   	function logout(){
   	var uname = document.getElementById("user_name").innerText;
   	google.script.run.withFailureHandler(not_logged).withSuccessHandler(show_login).LOGOUT(uname);
   	}
   	function show_login(url){
   	window.open(url, '_top');
   	}
   	function not_logged(){
   	alert("test");
   	}
   	</script>

   view raw lp_SCRIPT.html hosted with ❤ by GitHub

This is a very quick setup or scripting session for this demo. This is enough to develop your application with same approach. This is foundational you may tweak a little to some degree. To learn more about Google Apps Script, click Buy Now button below. It's a good reference for your study.

Google Apps Script Simple Weather API Integration

Here's a simple approach to weather API integration with Google Apps Script. The objective of this script is fetch weather forecast data from a free API provider Openweathermap. The API provides you the basic weather information from historical, current and even a 30 day forecast. You need to register to this API first you to have access to the free resources. which is just enough to get you running.

Apps Script

In the script, shown the basic steps to build that approach to properly call the API and return the desired weather information you might need in your applications. For a successful run you will have below as the return values to choose from. You can create another function to fetch desired weather information.

The Script:

	[20-07-18 09:37:13:462 HKT] Target OpenWeatherOrg City ID : 1850147
	[20-07-18 09:37:13:511 HKT] Weather description : shower rain
	[20-07-18 09:37:13:513 HKT] {main={temp_min=293.15, humidity=100.0, pressure=1012.0, feels_like=294.33, temp=293.15, temp_max=293.15}, dt=1.595035815E9, id=1850147.0, name=Tokyo, coord={lat=35.69, lon=139.69}, clouds={all=75.0}, wind={speed=3.6, deg=50.0}, cod=200.0, timezone=32400.0, sys={sunrise=1.595014718E9, id=8077.0, sunset=1.595066154E9, type=1.0, country=JP}, weather=[{icon=09d, main=Rain, id=521.0, description=shower rain}, {description=mist, icon=50d, main=Mist, id=701.0}], rain={1h=13.97}, base=stations, visibility=5000.0}

view raw openweather_return.js hosted with ❤ by GitHub

	function main(){
	//base url
	var api_url = 'http://api.openweathermap.org/data/2.5/weather?';
	//Write your openweather.org api key here
	var api_key = 'XXXXXXXXXXXXXXXXXXX';//<---openweather API key here
	//Get's city open weather org id
	var open_weather_org_id = GETCITYOPENWEATHERMAPID('Tokyo');//<---Sample city
	Logger.log('\t\tTarget OpenWeatherOrg City ID : ' + open_weather_org_id);
	//Get's current city open weather data
	var weather_data = CURRENTWEATHER(open_weather_org_id,api_url,api_key);
	Logger.log('\t\tWeather description : ' + weather_data.weather[0].description);
	Logger.log(weather_data);
	}
	/*
	THIS IS A JSON FORMAT FOR CITIES LIST AND ITS CORRESPONDING OPENWEATHERMAP IDS.
	YOU CAN FIND OTHER CITY DETATILS IN THIS SITE (http://web.archive.org/web/20180619015316/http://openweathermap.org/help/city_list.txt)
	*/
	function GETCITYOPENWEATHERMAPID(city){
	var AdWord_Map = {
	"Hokkaido":[{"Criteria_ID":20624, "Name":"Hokkaido", "Canonical_Name":"Hokkaido,Japan", "Prefecture":"Hokkaido", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":2128295}],
	"Saitama":[{"Criteria_ID":20634, "Name":"Saitama Prefecture", "Canonical_Name":"Saitama Prefecture,Japan", "Prefecture":"Saitama", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":6940394}],
	"Chiba":[{"Criteria_ID":20635, "Name":"Chiba Prefecture", "Canonical_Name":"Chiba Prefecture,Japan", "Prefecture":"Chiba", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":2113015}],
	"Tokyo":[{"Criteria_ID":20636, "Name":"Tokyo", "Canonical_Name":"Tokyo,Japan", "Prefecture":"Tokyo", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":1850147}],
	"Kanagawa":[{"Criteria_ID":20637, "Name":"Kanagawa Prefecture", "Canonical_Name":"Kanagawa Prefecture,Japan", "Prefecture":"Kanagawa", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":1848354}],
	"Niigata":[{"Criteria_ID":20638, "Name":"Niigata", "Canonical_Name":"Niigata,Japan", "Prefecture":"Niigata", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":1855431}],
	"Toyama":[{"Criteria_ID":20639, "Name":"Toyama Prefecture", "Canonical_Name":"Toyama Prefecture,Japan", "Prefecture":"Toyama", "Parent_ID":2392, "Country_Code":"JP", "Target_Type":"Prefecture", "Status":"Active", "Open_Weather_Map_ID":1849876}],
	};
	return AdWord_Map[city][0].Open_Weather_Map_ID.toString();
	}
	function CURRENTWEATHER(city_id,api_url,api_key){
	//Combines the credentials to complete the desired api url
	var api_url_id = api_url + 'id=' + city_id + '&appid=' + api_key;
	//Fetch the url from api link
	var response = UrlFetchApp.fetch(api_url_id.toString());
	var json = response.getContentText();
	var weather_data = JSON.parse(json);
	return weather_data;
	}

view raw openweather_v0.js hosted with ❤ by GitHub

Google AdWord application

Aside from using it for a weather applications, there's a clever user for this in Google AdWords campaign strategy. Let's say you are run a campaign for a product tied up to a specific season and for that matter, generate's demand for certain weather. 

The key information from above script is the return value weather description(i.e., Sunny, Cloudy or Rainy). AdGroup names could be tailored containing the same weather description for easy script manipulation. You could iterate all your adgroups to detect those names.

There you have it. A quick and simple foundation of this automation. I have a working script that encompass the full AdWord dynamics. For Advanced Google AdWords, this will be a useful approach to optimize your campaigns and protect your budgets. Leave a comment below for your thoughts.

VBA: Here's How to Crawl an Amazon Product Reviews

Web Scraping, a clever approach to extract information from layers and layers of html of a web page. It's a a crude way and an alternative to some developers who don't the right access to an API. I'm sharing to an approach if you need to scrape an Amazon review of a product.

The script that I'm going to share is good only for single product URL scraping. This is to demonstrate how VBA can still be effective for those low tier automation implementation. Basically, applicable to developers still in exploring phase, students and hobbyist just want to have some fun.

I've taken a simple approach and haven't taken time to document. This is a project just came to at instance decided to write this in Excel VBA. Depending on the number reviews in that product, the script will take 2~3 mins to run. Let me know in the comments below your thoughts. 

To run the code, you need to select "GETALLREVIEWS" in your macro's list and you will be prompted with an input box where you can paste the URL of the target product listed in Amazon. The script will return to you a new spreadsheet with the list of the reviews successfully scraped.

The programming language used in this application is VBA(Visual Basic for Application). If you want to learn this coding technique, click Buy Now button below for a book to guide you with your learning.

Here's the script. Have fun

	Attribute VB_Name = "Amazon_Reviews_Scrape"
	Function GETPAGEREVIEWSURL(href As String, pageNum As Variant) As String
	Dim splitter As Variant
	splitter = Split(href, "/")
	splitter(4) = "product-reviews"
	If pageNum = 1 Then
	splitter(6) = "ref=cm_cr_dp_d_show_all_btm?ie=UTF8&reviewerType=all_reviews"
	ElseIf pageNum = 2 Then
	splitter(6) = "ref=cm_cr_arp_d_paging_btm_next_" & pageNum & "?ie=UTF8&reviewerType=all_reviews&pageNumber=" & pageNum
	Else
	splitter(6) = "ref=cm_cr_getr_d_paging_btm_next_" & pageNum & "?ie=UTF8&reviewerType=all_reviews&pageNumber=" & pageNum
	End If
	GETPAGEREVIEWSURL = Join(splitter, "/")
	End Function
	Function GETPROD_AMAZONPARAM(href As String) As Object
	Dim param As New Collection
	Dim splitter As Variant
	splitter = Split(href, "/")
	param.Add splitter(5)
	param.Add splitter(3)
	Set GETPROD_AMAZONPARAM = param
	End Function
	Function GETREVIEWURL(ASIN As String, ID As String) As String
	GETREVIEWURL = "https://www.amazon.com/gp/customer-reviews/" & ID & "/ref=cm_cr_arp_d_rvw_ttl?ie=UTF8&ASIN=" & ASIN
	End Function
	Function GETREVIEWSCORE(score_string As String) As Integer
	Dim splitter As Variant
	splitter = Split(score_string, " out of ")
	GETREVIEWSCORE = CInt(splitter(0))
	End Function
	Function GETREVIEWCOUNTRY_DATE(countryDateString As String) As Object
	Dim revieTiming As New Collection
	Dim splitter As Variant
	Dim inTheSplitCheck As Variant
	splitter = Split(countryDateString, " on ")
	revieTiming.Add splitter(1)
	inTheSplitCheck = Split(splitter(0), " in the ")
	If UBound(inTheSplitCheck) = 0 Then inTheSplitCheck = Split(splitter(0), " in ")
	revieTiming.Add inTheSplitCheck(1)
	Set GETREVIEWCOUNTRY_DATE = revieTiming
	End Function
	Function GETUPVOTECOUNT(upvoteStr As String) As Integer
	Dim splitter As Variant
	splitter = Split(upvoteStr, " ")
	If splitter(0) = "One" Then splitter(0) = 1
	GETUPVOTECOUNT = CInt(splitter(0))
	End Function
	Function GETREVIEWSPAGESCOUNT(el As MSHTML.IHTMLElement) As Integer
	Dim el_innerText As String
	Dim splitter As Variant
	Dim pageCount As Integer
	el_innerText = el.innerText
	splitter = Split(el_innerText, " of ")
	splitter = Split(splitter(1), " ")
	pageCount = CInt(splitter(0))
	If pageCount Mod 10 > 0 Then pageCount = ((pageCount / 10) - (pageCount Mod 10 / 10)) + 1
	GETREVIEWSPAGESCOUNT = pageCount
	End Function
	Function GETREVIEWIDS(href As String) As Object
	Dim href1 As String
	Dim IE As New SHDocVw.InternetExplorer, IE2 As New SHDocVw.InternetExplorer
	Dim HTMLDoc As MSHTML.HTMLDocument, HTMLDoc2 As MSHTML.HTMLDocument, HTMLDoc3 As MSHTML.HTMLDocument
	Dim HTMLDocElements As MSHTML.IHTMLElementCollection
	Dim HTMLDocElement As MSHTML.IHTMLElement
	Dim REVIEW_IDS As New Collection
	Dim US_TOTAL_PAGE_REVIEWS As Integer
	'THIS IS THE INITIAL URL CRAWL OF THE MAIN PRODUCT PAGE
	IE.navigate href
	IE.Visible = False
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading page main project page..."
	Loop
	Set HTMLDoc = IE.document
	'GET THE INTERNATIONAL REVIEWS ID
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE INTERNATIONAL REVIEWS ARE CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-section review aok-relative cr-desktop-review-page-0")
	If HTMLDocElements.Length <> 0 Then
	Debug.Print vbCr & "International Reviews" & vbCr & "-----------------------------"
	Application.StatusBar = "Extracting International Review IDs..."
	'ITERATE EACH HTML ELEMENTS COLLECTED
	For Each HTMLDocElement In HTMLDocElements
	'COLLECT THE EXTRACTED REVIEW ID
	REVIEW_IDS.Add HTMLDocElement.getAttribute("id")
	Debug.Print HTMLDocElement.getAttribute("id")
	Next HTMLDocElement
	End If
	'CAPTURE THE HIDDEN FRAMES WHERE SOME OF THE INTERNATIONAL REVIEWS ARE CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-section review aok-relative cr-desktop-review-page-1 aok-hidden")
	If HTMLDocElements.Length <> 0 Then
	Debug.Print vbCr & "Hidden International Reviews" & vbCr & "-----------------------------"
	Application.StatusBar = "Extracting Hidden International Review IDs..."
	'ITERATE EACH HTML ELEMENTS COLLECTED
	For Each HTMLDocElement In HTMLDocElements
	'COLLECT THE EXTRACTED REVIEW ID
	REVIEW_IDS.Add HTMLDocElement.getAttribute("id")
	Debug.Print HTMLDocElement.getAttribute("id")
	Next HTMLDocElement
	End If
	IE.Quit
	'START OF US REVIEWS CRAWL
	'CONVERT THE INITIAL URL ABOVE AND CONVERTS IT TO THE PRODUCT REVIEW PAGE 1
	'THIS IS NEEDED TO CAPTURE EXPECTED NUMBER REVIEW PAGES
	href1 = GETPAGEREVIEWSURL(href, 1)
	IE2.navigate href1
	IE2.Visible = False
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE2.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading page..."
	Loop
	Set HTMLDoc2 = IE2.document
	'GET THE EXPECTED NUMBER OF US REVIEW PAGES
	Set HTMLDocElements = HTMLDoc2.getElementsByClassName("a-size-base")
	For Each HTMLDocElement In HTMLDocElements
	Dim x_test As String
	x = HTMLDocElement.getAttribute("data-hook")
	If HTMLDocElement.getAttribute("data-hook") = "cr-filter-info-review-count" Then
	US_TOTAL_PAGE_REVIEWS = GETREVIEWSPAGESCOUNT(HTMLDocElement)
	Exit For
	End If
	Next HTMLDocElement
	'CRAWL THE FIRST 10 US REVIEWS
	'CAPTURE THE ELEMENT WHERE THE FIRST 10 US REVIEW IDS ARE CONTAINED
	Set HTMLDocElements = HTMLDoc2.getElementsByClassName("a-section review aok-relative")
	Debug.Print vbCr & "First 10 US Reviews" & vbCr & "-----------------------------"
	Application.StatusBar = "Extracting First 10 US Review ID's..."
	'ITERATE EACH HTML ELEMENTS COLLECTED
	For Each HTMLDocElement In HTMLDocElements
	'COLLECT THE EXTRACTED REVIEW ID
	REVIEW_IDS.Add HTMLDocElement.getAttribute("id")
	Debug.Print HTMLDocElement.getAttribute("id")
	Next HTMLDocElement
	'TERMINATES IE
	IE2.Quit
	'CRAWL THE NEXT REVIEW PAGES FOR ITS REVIEW IDS
	'GET THE US REVIEW IDS
	For i = 2 To US_TOTAL_PAGE_REVIEWS
	'TRANSFORMS THE INITIAL URL TO MATCH EACH REVIEW PAGE URL
	href1 = GETPAGEREVIEWSURL(href, i)
	Debug.Print vbCr & "Crawling Page " & i & "..." & vbCr & "-----------------------------"
	Dim IE3 As New SHDocVw.InternetExplorer
	IE3.navigate href1 '
	IE3.Visible = False
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE3.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading Review pages..."
	Loop
	Set HTMLDoc3 = IE3.document
	'COLLECTS THE ELEMENT WITH CLASS NAME THAT CONTAINTS THE REVIEW ID
	Set HTMLDocElements = HTMLDoc3.getElementsByClassName("a-section review aok-relative")
	Application.StatusBar = "Extracting Rest of the US Review ID's..."
	'ITERATES EACH ELEMENTS
	For Each HTMLDocElement In HTMLDocElements
	REVIEW_IDS.Add HTMLDocElement.getAttribute("id")
	Debug.Print HTMLDocElement.getAttribute("id")
	Next HTMLDocElement
	Next i
	IE3.Quit
	Set GETREVIEWIDS = REVIEW_IDS
	End Function
	Function GETTHEREVIEWS(href As String) As Object
	Dim PROD_AMAZONPARAM As New Collection
	Dim REVIEW_IDS As New Collection
	Dim REVIEW_ID As Variant
	Dim ASIN As String
	Dim PROD_DESC As String
	Dim DATABASE_PATH As String
	Dim i As Integer
	DATABASE_PATH = GET_USERDBPATH
	Set REVIEW_IDS = GETREVIEWIDS(href)
	Set PROD_AMAZONPARAM = GETPROD_AMAZONPARAM(href)
	ASIN = PROD_AMAZONPARAM(1)
	'Product Description as stated in the href
	PROD_DESC = PROD_AMAZONPARAM(2)
	Debug.Print vbCr & "Extracting the Reviews Info..." & vbCr & "-----------------------------"
	i = 1
	For Each REVIEW_ID In REVIEW_IDS
	Dim href1 As String
	Dim IE As New SHDocVw.InternetExplorer
	Dim HTMLDoc As MSHTML.HTMLDocument
	Dim HTMLDocElements As MSHTML.IHTMLElementCollection
	Dim HTMLDocElement As MSHTML.IHTMLElement
	Dim REVIEW_URL As String
	Dim REVIEWER_PROFILE_NAME As String
	Dim REVIEW_SCORE As Integer
	Dim REVIEW_TIMING As New Collection
	Dim REVIEW_DATE As String
	Dim REVIEW_COUNTRY As String
	Dim PRODUCT_MODEL As String
	Dim BADGE_INFO As String
	Dim REVIEW_TITLE As String
	Dim REVIEW_COMMENT As String
	Dim UPVOTES As Integer
	Dim REVIEW_NUM_OF_COMMENTS As Integer
	Dim DID_MANUFACTURER_RESPONDED As String
	Dim MANUFACTURERS_RESPONSE As String
	Dim MANUFACTURERS_REPLIES As New Collection
	UPVOTES = 0
	REVIEW_NUM_OF_COMMENTS = 0
	DID_MANUFACTURER_RESPONDED = "N"
	MANUFACTURERS_RESPONSE = vbNullString
	REVIEW_URL = GETREVIEWURL(ASIN, CStr(REVIEW_ID))
	'OPEN THE REVIEW URL
	IE.navigate REVIEW_URL
	'THIS IS TO MAKES THE PAGE IS LOADED BEFORE SCRAPING FOR INFO
	Do While IE.readyState <> READYSTATE_COMPLETE
	Application.StatusBar = "Loading individual review page..."
	Loop
	Set HTMLDoc = IE.document
	'GET THE REVIEW PAGE ESSENTIAL INFO
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE PROFILE NAME IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-profile-name")
	REVIEWER_PROFILE_NAME = HTMLDocElements(0).innerText
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE SCORE IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-icon-alt")
	REVIEW_SCORE = GETREVIEWSCORE(HTMLDocElements(0).innerText)
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE REVIEW TITLE IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-base a-link-normal review-title a-color-base review-title-content a-text-bold")
	REVIEW_TITLE = HTMLDocElements(0).innerText
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE REVIEW DATE AND COUNTRY IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-base a-color-secondary review-date")
	Set REVIEW_TIMING = GETREVIEWCOUNTRY_DATE(HTMLDocElements(0).innerText)
	REVIEW_DATE = REVIEW_TIMING.Item(1)
	REVIEW_COUNTRY = REVIEW_TIMING.Item(2)
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE PRODUCT MODEL IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-mini a-link-normal a-color-secondary")
	If HTMLDocElements.Length <> 0 Then PRODUCT_MODEL = HTMLDocElements(0).innerText
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE PROFILE BADGE IS CONTAINED
	'CHECK FOR AVP-BADGE = Verified Purchase
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-mini a-color-state a-text-bold")
	'CHECK FOR VINE VOICE INFO = Vine Customer Review of Free Product
	If HTMLDocElements.Length = 0 Then Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-color-success a-text-bold")
	'CHEDK IF THERE ARE BADGES PRESENT
	If HTMLDocElements.Length = 0 Then
	BADGE_INFO = ""
	Else
	BADGE_INFO = HTMLDocElements(0).innerText
	End If
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE REVIEW TEXT IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-base review-text review-text-content")
	REVIEW_COMMENT = WorksheetFunction.Trim(WorksheetFunction.Clean(HTMLDocElements(0).innerText))
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE UPVOTE INFO IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-size-base a-color-tertiary cr-vote-text")
	'CHECK IF THERE ARE REVIEWS UPVOTES
	If HTMLDocElements.Length <> 0 Then UPVOTES = GETUPVOTECOUNT(HTMLDocElements(0).innerText)
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE NUMBER OF COMMENTS IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("review-comment-total aok-hidden")
	If HTMLDocElements.Length <> 0 Then REVIEW_NUM_OF_COMMENTS = CInt(HTMLDocElements(0).innerText)
	'CAPTURE THE CLASS NAME OF THE SAMPLE WHERE THE MANUFACTURER'S COMMENTS/REPLY IS CONTAINED
	Set HTMLDocElements = HTMLDoc.getElementsByClassName("a-box a-spacing-large official-comment-container")
	If HTMLDocElements.Length <> 0 Then
	For Each HTMLDocElement In HTMLDocElements
	MANUFACTURERS_REPLIES.Add HTMLDocElement.innerText
	Next HTMLDocElement
	End If
	If MANUFACTURERS_REPLIES.Count <> 0 Then
	DID_MANUFACTURER_RESPONDED = "Y"
	MANUFACTURERS_RESPONSE = MANUFACTURERS_REPLIES(1)
	End If
	Application.StatusBar = "Extracting Reviews by ID " & Format(i / REVIEW_IDS.Count, "0%") & " complete"
	Debug.Print ASIN, PROD_DESC, PRODUCT_MODEL, REVIEW_ID, REVIEW_URL, REVIEW_COUNTRY, REVIEW_DATE, REVIEWER_PROFILE_NAME, BADGE_INFO, REVIEW_SCORE, REVIEW_TITLE, UPVOTES, REVIEW_COMMENT, REVIEW_NUM_OF_COMMENTS, DID_MANUFACTURER_RESPONDED, MANUFACTURERS_RESPONSE
	Dim REVIEW_DATA As New Collection
	REVIEW_DATA.Add ASIN
	REVIEW_DATA.Add PROD_DESC
	REVIEW_DATA.Add PRODUCT_MODEL
	REVIEW_DATA.Add REVIEW_ID
	REVIEW_DATA.Add REVIEW_URL
	REVIEW_DATA.Add REVIEW_COUNTRY
	REVIEW_DATA.Add REVIEW_DATE
	REVIEW_DATA.Add REVIEWER_PROFILE_NAME
	REVIEW_DATA.Add BADGE_INFO
	REVIEW_DATA.Add REVIEW_SCORE
	REVIEW_DATA.Add REVIEW_TITLE
	REVIEW_DATA.Add UPVOTES
	REVIEW_DATA.Add REVIEW_COMMENT
	REVIEW_DATA.Add REVIEW_NUM_OF_COMMENTS
	REVIEW_DATA.Add DID_MANUFACTURER_RESPONDED
	REVIEW_DATA.Add MANUFACTURERS_RESPONSE
	WRITETOSHEET REVIEW_DATA
	Set REVIEW_DATA = Nothing
	i = i + 1
	Next REVIEW_ID
	IE.Quit
	End Function
	Sub WRITETOSHEET(passedData As Object)
	For i = 1 To passedData.Count
	ActiveCell.Value = passedData(i)
	ActiveCell.Offset(0, 1).Select
	Next i
	ActiveCell.Offset(1, (passedData.Count) * -1).Select
	End Sub
	Sub GETALLREVIEWS()
	Dim prodUrl As String
	prodUrl = InputBox("Please input product URL", "Scraping URL")
	If Len(prodUrl) = 0 Then
	MsgBox "You have not provided a valide url", vbCritical
	Exit Sub
	End If
	Dim ws As Worksheet
	Sheets.Add After:=ActiveSheet
	Set ws = ActiveSheet
	GETTHEREVIEWS prodUrl
	End Sub

view raw Amazon_Reviews_Scrape_v3.bas hosted with ❤ by GitHub

How To: Merge Multiple Workbooks or Sheets in to one

Productivity Tools

Have you ever encountered a file with table split into multiple tabs/sheets? Or, have you ever been in a situation, wherein, you need to merge same table from multiple Workbooks into a single file?

So, how do you merge those multiple sheets? Below are the 3 methods:

• Copy and paste all and append them into a single tab/sheet. That's is manual method. Just don't miss out to select a column or Columns. Or, miss a row or even Rows. This the same approach if you need to merge same tables from multiple workbooks. Tedious, but, what could you have done better.
• Power Query this an advance MS Excel feature made easy with a click of a button. It comes as an AddIn you can install on your spreadsheet. Click LINK to download the app from Microsoft. A complete GUIDE will introduce you Power Query and will post an article about merging spreadsheets with this feature.
• If you are not confident with advance tools and manual, tedious approach. I'm offering a Productivity Tool you can have with your MS Excel spreadsheets. It's a very simple to use and you can have them for only $10. Click Buy Now below to download the app.

After successful download go to LINK for the instructions on how to install this app in your MS Excel. The Feature will be in your Data tab with "Productivity Tools" name. Two features will be available for you to use, Merge Worksheets and Merge Workbooks.

My Top 4 Logitech Mouse

Let's pause for something and recognized the things that helped us out in our daily grind in the digital world. Here are my top 4 Logitech Optical mouse the got me through those crunches. Before we go to the list, the Criteria on how I arrived with these four: 

• Life span - does it outlive my PC?(Just kidding). This is describing if had it for 2 yrs or more.
• Frequency of repair - it does lived long. But, you have keep repairing it. Those Lousy Mouse Scroll Wheel should really not fail. 
• Comfortable to handle - this is self explanatory.
• Cost - Focus on value. Low Cost doesn't really tell the whole picture. But, it helps if cheap and reliable.

1	Logitech M525		I had this for 4 years. The rubber for grip is already worn out. But, it is still a horse. Responsiveness is still satisfactory
2	Logitech M238		This is Logitech's Doodle collection. It comes in various artwork and great for gift. The only drawback for me is the size.
3	Logitech M185		This is a very simple design. It's close with the M238 at number 3. I got this replaced after I saw the Doodles collection for it was fun to look at.
4	Logitech B100		A very conventional design and you don't have to worry about running out of power. As long as you are connected to the PC. This will not fail you. Bad experience is with the Lousy Scroll Wheel in the long use.

So, there you have it. This is not a really techy top 4. But, I'm proud of these for they have served, and still serving, me well.

How To: Make a calendar in excel

How to make a calendar in Excel. This could be a weird question for some. But, trust it's still valid. I still hear this question from an average Excel user and been using for years. We are talking of the method wherein we could just lay out full calendar for a month for a year. Or, for a number of years.

So, is there a quick way? The answer is, Yes. There is what you call templates. It's just a two step approach basically:

1. Open MS Excel
2. In here you have two options to get to templates
• When you have just opened Excel fresh, you'll be prompted with the list of the templates. In there, you get to select various templates. Including the Calendar templates
• On a currently opened Excel, go to Files>New then it will bring up you same templates view above

It's that simple! Now, the other question. Does excel have a feature where in, instead of typing a date in a cell. You will select the date from a calendar box? Well, in MS Excel currently distributed. There's none. But, you can download an AddIn for $10. Click below buy button for the AddIn.

After successful download go to LINK for the instructions on how to install on your MS Excel. It the AddIn will be shown in the Insert tab as Date & Time with a calendar icon.

Customized Charts AddIn for MS Excel

Have ever asked yourself, is there an available chart type in MS Excel for Pareto Chart? Are you tired of manually configuring the necessary formula to arrive the corresponding cumulative values and then selecting a chart and changing the the corresponding data series to arrive at the Column and Line chart configuration. What if you have the ability to just select the ranges and click a feature within Excel to create the Pareto chart you've want to generate quickly.

Click buy now button below to download the AddIn. For $10, you will have the ability on your MS Excel Sheets to create quick Pareto chart and a bonus custom chart Box and Whisker and xy Chart. These charts takes multiple steps to create conventionally. So, download now.
After successful download go to LINK for the instructions on how to install on your MS Excel. You will see the new feature on insert tab of your spreadsheet. With labels shown above image.

How To: Change Password PLDT Home Fibr router

If you reside in the Philippines and a PLDT DSL or Fibr subscriber. Here's a quick guide for you to change your router password. This guide is mostly helpful to DSL subscriber who have been provided with a RN104R4GC-2T2R-A5 Modem, you will thank me for this. Most installers just advice us not to share the default(device) password. Which is kind of weird as our first order with WiFi router is not having anyone capture the passwords. So, without further a do:

1. Connect your PC through the Ethernet cable. You have 4 ports on your router
2. Open a web browser on your PC.
3. Go to this URL http://192.168.1.1/super.htm. 
4. Enter these credentials 
• username - adminpldt
• password - qVuUKLmCJJhCF4YY3x9xqJy
5. Then you will be diverted to a similar page below. Now, with the RN104R4GC-2T2R-A5 Modem you 2 options for your WiFi signal. 2.4Ghz and 5Ghz and you can setup your password accordingly.
   

That's it!,quick and easy 5 steps. Stay tune for more setup instruction with the device.

Google ads script to Schedule your campaign

Running a campaign in Google Ad is both time consuming and money. I will not discuss the dynamics about this let just say every Ad campaigns you have there's an equivalent cost involve. The more you require results(i.e. clicks, views). The more Google ask from your pocket. So, what if you have a limited budget and you want to make sure you're within those limits with the need to continually access your Google Ad account. Let me share to you a Google Ad script to manage your campaign automatically to address the paint point. The Goal of this script is to;

1. Run at any point in time and check ad-groups that exceeds the target CPA and pause them
2. Re-enable those paused ad-groups at midnight
3. Send notification through email the status of those Ad-group/campaigns

Here's the complete code with the goals above in mind. I'm creating this in a very short time. Tested a bit and worked so far. Let me know for suggestions in this approach.

	var notificationEmailTo = "";//<----SET EMAIL ADDRESS FOR NOTIFICATION HERE
	function main() {
	/*
	THIS SCRIPT CAN RUN AN ANY POINT IN TIME AND CHECK ADGROUPS THE EXCEEDS TARGET CPA AND PAUSE THEM.
	AT MIDNIGHT, IT WILL REACTIVATE THOSE PAUSED ADGROUP
	*/
	var nDate = new Date();
	var hrs = nDate.getHours();
	var mins = nDate.getMinutes();
	if(hrs < 10){hrs = '0' + hrs;}
	if(mins < 10){mins = '0' + mins;};
	var strCurrTime = hrs + ":" + mins;
	//CHECK'S TIME IF MIDNIGHT
	if (strCurrTime === "00:00"){//MIDNIGHT SCRIPT SPECIFIC
	//RE ENABLE THE PAUSED ADGROUPS IF THERE'S ANY
	var re_enabled_adGroups = iterateAdGroups(true);
	}else{
	//COLLECT ALL GROUPS EXCEEDING TARGET CPA AND PAUSE THEM
	var paused_adGroups = iterateAdGroups(false);
	//IF THERE ARE ADGROUP/S EXCEEDS TARGET CPA
	if(paused_adGroups.length > 0){
	//SEND NOTIFICATION FOR PAUSED ADGROUPS
	notifyPausedAdGroups(paused_adGroups.PAUSED,notificationEmailTo,"Google AdWord Report - Paused AdGroups",initialHtmlBody());
	}
	}
	Logger.log(new Date().toLocaleTimeString());
	Logger.log(strCurrTime);
	}
	function iterateAdGroups(renableMode){
	/*
	THIS WILL GET ALL ADGROUP STATUS
	renableMode = false, PAUSE THOSE THAT EXCEEDS TARGET CPA
	renableMode = true, REACTIVATE THOSE PAUSED ADGROUPS
	*/
	//PAUSED ADGROUPS CONTAINER
	var pausedAdGroups = [];
	//REACTIVATED ADGROUPS CONTAINER
	var reActivatedAdGroups = [];
	//GET ALL ADGROUPS
	var adGroupIterator = AdsApp.adGroups().get();
	//CHECK'S IF THERE'S A NEXT GROUP IN THE ITERATION
	if (adGroupIterator.hasNext()){
	//GET ADGROUP
	var adGroup = adGroupIterator.next();
	//GET ADGROUP ID
	var adGroupId = adGroup.getId();
	//GET ADGROUP NAME
	var adGroupName = adGroup.getName();
	//GET ADGROUP STATS FOR YESTERDAY
	var adGroupStats = adGroup.getStatsFor("YESTERDAY");
	var COST = adGroupStats.getCost();
	var AVECPC = adGroupStats.getAverageCpc();
	var AVECPM = adGroupStats.getAverageCpm();
	var AVECPV = adGroupStats.getAverageCpv();
	var CTR = adGroupStats.getCtr();
	var CLICKS = adGroupStats.getClicks();
	var PAGEVIEWS = adGroupStats.getViews();
	var IMPRESSIONS = adGroupStats.getImpressions();
	var AVEPAGEVIEWS = adGroupStats.getAveragePageviews();
	var AVEPOSITION = adGroupStats.getAveragePosition();
	var AVETIMEONSITE = adGroupStats.getAverageTimeOnSite();
	var VIEWRATE = adGroupStats.getViewRate();
	var BOUNCERATE = adGroupStats.getBounceRate();
	var CONVERSIONRATE = adGroupStats.getConversionRate();
	var stats = {COST:COST,AVECPC:AVECPC,AVECPM:AVECPM,CTR:CTR,CLICKS:CLICKS,PAGEVIEWS:PAGEVIEWS,IMPRESSIONS:IMPRESSIONS,
	AVEPAGEVIEWS:AVEPAGEVIEWS,AVEPOSITION:AVEPOSITION,AVETIMEONSITE:AVETIMEONSITE,
	VIEWRATE:VIEWRATE,BOUNCERATE:BOUNCERATE,CONVERSIONRATE:CONVERSIONRATE};
	//GET ADGROUP BIDDING
	var CPA = adGroup.bidding().getCpa();//CPA
	var CPC = adGroup.bidding().getCpc();//CPC
	var CPM = adGroup.bidding().getCpm();//CPM
	//CHECK IF ADGROUP IS PAUSED
	var isAdGroupPaused = adGroup.isPaused();
	//GET ALL BIDDING INFO
	var bidding = {CPA:CPA,CPC:CPC,CPM:CPM};
	//IF collectPausedMode === true, COLLECT ALL PAUSED ADGROUP
	if (renableMode){
	if(isAdGroupPaused){
	adGroup.enable();
	}
	//COLLECT PAUSED ADGROUP INFO
	reActivatedAdGroups.push({ID:adGroupId,NAME:adGroupName,PAUSED:isAdGroupPaused,BIDDING:bidding,STATS:stats});
	}else{//IF collectPausedMode === false, CHECK FOR CPA IF EXCEEDS CPC AND PAUSE ADGROUP
	//CHECK'S IF CPA EXCEEDS CPC
	if ((stats.COST > bidding.CPA) && (isAdGroupPaused === false)){
	//PAUSE ADGROUP
	adGroup.pause();
	//COLLECT PAUSED ADGROUP INFO
	pausedAdGroups.push({ID:adGroupId,NAME:adGroupName,CPA:adGroupCPA,CPC:adGroupCPC,PAUSED:isAdGroupPaused});
	}
	}
	}
	var adGroupStats = {PAUSED:pausedAdGroups,REACTIVATED:reActivatedAdGroups};
	return adGroupStats;
	}
	function initialHtmlBody(){
	/*
	THIS COMPOSES THE REPORT NOTIFICATION
	TABLE TEMPLATE.
	TABLE HEADERS
	*/
	var htmlBody = "";
	htmlBody += "<!DOCTYPE html>";
	htmlBody += "<html>";
	htmlBody += "<head>";
	htmlBody += "<style>";
	htmlBody += "table {font-family: arial, sans-serif;border-collapse: collapse;width: 100%;}";
	htmlBody += "td, th {border: 1px solid #dddddd;padding: 8px;}";
	htmlBody += "td {text-align: left;}";
	htmlBody += "</style></head><body>";
	htmlBody += "<h2>Paused AdGroup summary</h2>";
	htmlBody += "<table>";
	htmlBody += "<tr><th colspan=3>AdGroup Info</th><th colspan=3>Bidding</th><th colspan=13>Yesterday Stats</th></tr>";
	htmlBody += "<tr><th>ID</th><th>Name</th><th>is Paused</th><th>CPA</th><th>CPC</th><th>CPM</th><th>Cost</th><th>Ave CPC</th>";
	htmlBody += "<th>Ave CPM</th><th>CTR</th><th>Clicks</th><th>Page Views</th><th>Impressions</th><th>Ave Page Views</th><th>Ave Position</th>";
	htmlBody += "<th>Ave Time on Site</th><th>View Rate</th><th>Bounce Rate</th><th>Converstion Rate</th></tr>";
	return htmlBody;
	}
	function notifyPausedAdGroups(adGroupStats,emailTo,emailSubject,initialHtmlBody){
	/*
	THIS WILL COMPOSE AN EMAIL AND SEND THE PAUSED ADGROUP REPORT
	*/
	//INITIAL EMAIL BODY REPORT TABLE
	var htmlBody = initialHtmlBody();
	//ITERATE THE PAUSED ADGROUPS COLLECTED
	for(var i=0; i<adGroupStats.length; i++){//pausedAdGroups.length; i++){
	var ID = adGroupStats.ID;
	var Name = adGroupStats.NAME;
	var isPaused = adGroupStats.PAUSED;
	var CPA = adGroupStats.BIDDING.CPA;
	var CPC = adGroupStats.BIDDING.CPC;
	var CPM = adGroupStats.BIDDING.CPM;
	var Cost = adGroupStats.STATS.COST;
	var AveCPC = adGroupStats.STATS.AVECPC;
	var AveCPM = adGroupStats.STATS.AVECPM;
	var CTR = adGroupStats.STATS.CTR;
	var Clicks = adGroupStats.STATS.CLICKS;
	var PageViews = adGroupStats.STATS.PAGEVIEWS;
	var Impressions = adGroupStats.STATS.IMPRESSIONS;
	var AvePageViews = adGroupStats.STATS.AVEPAGEVIEWS;
	var AvePosition = adGroupStats.STATS.AVEPOSITION;
	var AveTimeOnSite = adGroupStats.STATS.AVETIMEONSITE;
	var ViewRate = adGroupStats.STATS.VIEWRATE;
	var BounceRate = adGroupStats.STATS.BOUNCERATE;
	var ConversionRate = adGroupStats.STATS.CONVERSIONRATE;
	//COMPLETES THE REPORT TABLE
	htmlBody += "<tr><td>" + ID + "</td><td>" + Name + "</td><td>" + isPaused + "</td><td>" + CPA + "</td><td>" + CPC + "</td><td>" + CPM + "</td><td>" + Cost + "</td><td>" + AveCPC + "</td>";
	htmlBody += "<td>" + AveCPM + "</td><td>" + CTR + "</td><td>" + Clicks + "</td><td>" + PageViews + "</td><td>" + Impressions + "</td><td>" + AvePageViews + "/td><td>" + AvePosition + "</td>";
	htmlBody += "<td>" + AveTimeOnSite + "</td><td>" + ViewRate + "</td><td>" + BounceRate + "</td><td>" + ConversionRate + "</td></tr>";
	}
	htmlBody += "</table></body></html>";
	//SENDS THE NOTIFICATION/PAUSED ADGROUP REPORT
	MailApp.sendEmail({
	to: emailTo,
	subject: emailSubject,
	htmlBody: htmlBody,
	});
	}

view raw PROJECT_ADWORDS_043020.js hosted with ❤ by GitHub

How To: Learn Google Apps Script

Google Apps Script(GAS) is been here for a while and Google have been successful in enabling developers to be innovative in their various lineup of apps. From Office Productivity tools to web applications. So how to learn this? So, here are the steps I took to be confident with Google Apps Script(GAS). I've been using this methodology for 5 years now and earning enough money as a part time income.

1. Get a Google Account. I think this is a no brainier and for sure you already have an account.
2. Choose on what to achieve with this learning. As I've mentioned Google offers customization using this script with the Office productivity tools such as Sheets(which is the mostly used), Docs, Slides and etc. Or, you can create a standalone web application.
3. Learn javascript. Yes, javascript. Don't worry, it's not that intimidating. Trust me. When you come into actual coding with Google Apps Script, you will be happy to find out that, on Google Sheets, there's the favorite feature of Macro recording. Yes, you heard me right. It's convenient. 

So, those are the 3 simple steps and stay tune on my blog for more articles about this topic. Thank you and have a good day.

I'm back!

It's been, really, a while since my last post. It's nice to get back into blogging. I've always promised myself to spend some time with my blog and share what I have learned from the past 10 years(wow!! it's that long).

What to expect?

I'm gonna be going into technical articles and how to's. If you're into coding, this blog will be for you. I'm gonna be sharing from VBA, javascript, Google Apps Script, Google AdWords script and many more. Practical and quick scripts/solutions in this fast paced environment.

See you in my future posts.