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):

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:

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:

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:
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:

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:

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

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.

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.