Google Images earlier offered a useful “search by size” option in advanced search to help you find logos, wallpapers and other images on the Internet by their exact size (or resolution).
For instance, you could limit your search for landscape photographs to image files that were at least 10 Megapixels in size. Or, if you are were using Google Image search to find wallpapers for the desktop, you could specify the image resolution as 1920x1080 pixels and Google would only return large images with those exact dimensions.
The “exact size” search option is no longer available in Google Image Search but you can still limit your image searches to a particular size by using the secret imagesize search operator in the query itself.
Here’s how.
Go to images.google.com and enter the search terms as before. Then append imagesize:WIDTHxHEIGHT to your query and hit Enter. Google Images will remove the operator from the query but the results will only display images that match the specified size.
The search by size operators works on the mobile version of Google as well so you may use the simple trick to find that perfect size wallpaper for your phone.
Google Apps Script makes it is easy for you to integrate data and functionality from Gmail, Google Drive, Google Maps, YouTube, and most other Google APIs. Apps Script is JavaScript under the hood so you don’t have to learn a new language and you don’t have to manage any servers since all your code runs on the Google Cloud, not your browser.
In this video tutorial, you’ll learn how to develop Google Apps Script projects locally on your computer inside Visual Studio Code. You can write your code in modern JavaScript, neatly organized in modules, and the build environment will use Babel and Webpack to transform your code into a version of JavaScript that is compatible with Apps Script.
Modern Development with Google Apps Script
There are quite a few advantages with having a local development environment vis-a-vis writing code in the Apps Script Cloud IDE.
You can write code with ES6 Classes, Arrow Functions, Modules, Destructing and use all the other modern JavaScript features.
The development experience inside VS Code is unmatched and tools like ESLint and Prettier make it easier for you to catch errors early in the development.
The build and deployment process can be completely automated with npm scripts and CLASP, Google’s command line utility for Apps Script.
VS Code has built-in support for Git and integrates with source control providers like Github and Gitlab. It is therefore easier to track changes and restore previous versions of the code.
You can quickly integrate JavaScript libraries like LoDash, Moment, Underscore and any of the NPM packages into your code.
You can use modern frameworks like React, Vue.js and Angular to build the HTML frontend that connects to the backend with the Google Script Client API.
Getting Started with the Apps Script Starter
The Starter kit is a boilerplate for quickly getting started with local Apps Script development locally inside VS Code. Open your terminal and run the following commands:
3. Install all the project dependencies and utilities
npm install
4. Connect CLASP to your Google account
npx clasp login
5. Create a new Google Apps Script project in your Google Drive with CLASP
npx clasp create --title "My Project" --rootDir ./dist --type standalone
This command will create a new .clasp.json file in your project folder that links the local folder with your Apps Script project. During build, Webpack will bundle all your code in a single JavaScript file and add it to the ./dist folder that Clasp will push to your Apps Script project.
Next, open the current project folder inside VS Code with the code . command. It includes some sample code but we will start with a blank folder so delete everything that’s inside the src folder.
Inside the src folder, create a new file - email.js - and write a simple arrow function that prints a list of all the email addresses connected to your Gmail account.
Next, create an index.js file (entry point) in the src folder, import the email function that you’ve created inside the email.js file and add it to the global object. This is a requirement of the Webpack plugin for Google Apps Script.
You can also add a function expression to the global object directly, like doGet in the example below.
Now that your JavaScript code is ready, open the appsscript.json file in your project folder and modify the oAuthScopes property to only include the scopes that are required by your project.
Next, jump to the command line terminal and run the deploy command to push your code to the Apps Script project.
npm run deploy
If you are deploying the project for the first time, you’ll get a prompt saying “Manifest file has been updated. Do you want to push and overwrite? (y/N)” - say yes.
After the deployment is complete, open the associated script in the browser with the CLASP open command.
npx clasp open
Inside the Apps Script Editor, go to the Run menu and choose the getEmailAddress function from the list. Open the logs and you should see your email addresses in the window.
Then go to the Publish menu, choose Deploy as web app and open the URL in a new browser tab to check the program output. That’s how easy it is to build projects with the Google Apps Script starter kit.
Using Git with Google Apps Script
Create a new repository in Github and make a note of the URL of the new repository. Next, open the terminal and run the following commands to push your Apps Script project to Github.
The Mail Merge app merges data from a Google Spreadsheet and sends them as personalized emails. You can format your sheet data in multiple colors, choose different font families, vary the size of your text, include hyperlinks, line breaks and more.
The rich text of spreadsheet cells is internally translated into HTML tags with inline CSS and thus the cell formatting is preserved in the outgoing Gmail messages. Here’s an example:
If you would like to enable this feature, go to the Add-ons menu in Google Sheets > Mail Merge with Attachments > Configure Mail Merge and check the “Preserve Cell Formatting” option.
You can even format your spreadsheet cells with conditional formatting and the text styles will be retained in mail merge. For instance, you can dynamically color the invoice amount column in red and make it bold if the due date has passed and this value would show up in bold red in the email message as well.
Send Rich Text HTML Emails with Google Sheet
This snippet handles the transformation of rich-text Spreadsheet data to HTML. The functions reads the data from a cell, specified in A1 notation, breaks the rich text into blocks that have the same text styles and translate individual blocks into HTML tags.
constsendRichEmail=()=>{const cellAddress ='A1';const sheetName ='Mail Merge';const recipient ='amit@labnol.org';const richTextValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(cellAddress).getRichTextValue();/* Run is a stylized text string used to represent cell text.
This function transforms the run into HTML with CSS
*/constgetRunAsHtml=(richTextRun)=>{const richText = richTextRun.getText();// Returns the rendered style of text in a cell.const style = richTextRun.getTextStyle();// Returns the link URL, or null if there is no link// or if there are multiple different links.const url = richTextRun.getLinkUrl();const styles ={
color: style.getForegroundColor(),'font-family': style.getFontFamily(),'font-size':`${style.getFontSize()}pt`,'font-weight': style.isBold()?'bold':'','font-style': style.isItalic()?'italic':'','text-decoration': style.isUnderline()?'underline':'',};// Gets whether or not the cell has strike-through.if(style.isStrikethrough()){
styles['text-decoration']=`${styles['text-decoration']} line-through`;}const css = Object.keys(styles).filter((attr)=> styles[attr]).map((attr)=>[attr, styles[attr]].join(':')).join(';');const styledText =`<span style='${css}'>${richText}</span>`;return url ?`<a href='${url}'>${styledText}</a>`: styledText;};/* Returns the Rich Text string split into an array of runs,
wherein each run is the longest possible
substring having a consistent text style. */const runs = richTextValue.getRuns();const htmlBody = runs.map((run)=>getRunAsHtml(run)).join('');
MailApp.sendEmail(recipient,'Rich HTML Email','',{ htmlBody });};
Known Limitations
You can format the cells of your Google Spreadsheet in any font family - from the cursive Caveat to the heavy Impact typeface - but if the recipient doesn’t have these fonts installed on their computer, the rendered text in the email would fallback to the default font.
The font colors, font size and text styles (bold, italics, underline) get perfectly transformed into HTML but other properties like background fill colors, borders and text-alignment of the cell are ignored.
Also, if your spreadsheet cells are formatted as dates, the rich text functions may not work.
This tutorial describes how you can easily send HTML emails in Gmail without using any extensions. You can format your Gmail messages to include tables, buttons, custom fonts, social media icons, wrap images around text, and more. A little knowledge of HTML and CSS will come handy but it is not a pre-requisite.
The built-in WYSIWYG editor of Gmail offers basic formatting options - you can make text bold, create lists, change font colors - but that’s pretty much it. There’s no option to insert custom HTML into the message body that is required to send rich emails though Gmail.
Write HTML Emails Directly in Gmail
Let’s start with some basic examples and then we move on to more advanced example where you’ll learn how to send email newsletters that were created separately inside MailChimp.
Insert Buttons in Gmail
This HTML snippet creates a beautiful call-to-action button with a blue background, slightly rounded edges and rendered in the popular Roboto font.
<ahref="https://digitalinspiration.com/"style="background-color:#1a73e8;padding:10px 20px;color: white;text-decoration:none;font-size:15px;font-family:Roboto,sans-serif;border-radius:5px;display:block;margin:20px 0;width: 120px">Explore our work
</a>
We cannot copy-paste this HTML directly into the email editor of Gmail but with the magic of Chrome Dev Tools, we can. Let’s see how:
Open gmail.com and compose a new email message. Add an emoji in the message body to be replaced with the HTML button. Right-click the emoji in the Gmail editor and choose Inspect.
This opens the Developer tools in the bottom half of the browser. Select the <img> tag that contains the emoji, right-click and choose Editor HTML. Replace the selected content with the button HTML and click anywhere outside the dev tools window.
You’ll now see a beautiful HTML button rendered in your email message with all the CSS styles and formatting. Watch the video for a more detailed tutorial.
Insert HTML Tables in Gmail
In the next example, we will embed an HTML table in the Gmail editor. Unlike our button above that had all styles inlined, the CSS of the table is contained in a separate stylesheet.
Therefore, prior to pasting the pasting the table HTML into Gmail, we need to “inline” the styles else the formatting will be ignored by Gmail. This can be easily done through Juice - simply paste the HTML and CSS in the input box and it will inline all the CSS styles in the HTML tags that is compatible with Gmail.
Send Email Newsletters with Gmail
Until now we have seen examples of adding basic HTML blocks inside Gmail but wouldn’t it be nice if we could send professional and responsive email newsletters through Gmail?
If you are new, the term responsive essentially means that the styles change automatically based on the device of the user. So if someone is viewing the email on a mobile phone, they would see a different layout than a person who has opened your email on a desktop computer.
You can use MailChimp or any other email template designer to create the newsletter. The only requirement is that the tool should have an option to download the designs as an HTML file.
You can feed this HTML file into Juice to inline the CSS styles and then insert the transformed HTML into Gmail using the emoji trick. The draft email newsletter can also be used as a template for sending personalized emails with Mail Merge.
The Email Extractor app pulls emails addresses of your contacts from Gmail into a Google Sheet. You can then use Mail Merge or Document Studio to send personalized emails to your contacts from within the sheet itself.
That said, the imported mailing list may sometimes have duplicate email addresses and it is thus be a good idea to clean up the data in your Google Sheet by removing duplicates before sending out the emails.
Highlight Duplicates in Google Sheets
You can use Conditional Formatting in Google Sheets combined with the COUNTIF formula to quickly highlight cells that contain duplicate values.
Here’s how you can find duplicates in Google Sheets:
Open the Google Sheet containing your data and go to the Format menu.
Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.
In the Apply to Range input box, add the range of cells that contain possible duplicates. In our case, the email addresses are in column A so we can put A:A to specify the entire A column.
Under the ‘Format cells if’ section, choose “Custom formula is” from the dropdown list as set the formula as =COUNTIF(A:A, A1) > 1
Click the Done button and you’ll instantly notice that all duplicate cells are highlighted as shows in the screenshot below.
The COUNTIF Function
The COUNTIF function in Google sheets (and Microsoft Excel) essentially counts the number of cells in the range that meet a specific criteria. For instance =COUNTIF(A:A, "apple") will count the number of cells that contain the word apple.
It can accept wildcard characters too so =COUNTIF(A:A, "apple?") will count cells that contain the word apple or apples. Or say =COUNTIF(A:A, "*@gmail.com") and it will highlight all email address that end with a gmail address.
Please note that the COUNTIF function is case-insensitive so values like gmail.com and Gmail.com are seen as duplicates.
Highlight Entire Row Containing Duplicates
If you’ve noticed in the previous screenshot, only specific cells that have duplicate values are highlighted through conditional formatting.
However, if you would like the Google Sheet to highlight the entire spreadsheet row that contains duplicate values, we need to slightly tweak the formatting rule.
Go to the Apply to Range input box and specify the entire spreadsheet range, not just the column that contains duplicates.
In the custom formula,use absolute reference for the range and also change criterion to use $A1 instead of A1. When we use $A1, we are telling Google Sheet to only change the row but lock the column.
The new duplicate detection formula reads =COUNTIF($A$1:$C$50, $A1)>1
Compare Multiple Columns for Duplicates
If you would like to determine duplicates by comparing data in multiple columns of the Google Sheet, use COUNTIFS instead of COUNTIF.
For instance, if column A is Email Address and column B is Company Name and you would like highlight duplicates cell only when the combination of values in Column A and B is identical, the new formula can be written as =COUNTIFS(A:A, A1, B:B, B1)>1
Remove Duplicate Rows in Google Sheets
Now that we have figured out a simple method to highlight duplicates in Google Sheets, the next task is to remove all duplicate rows.
There are two ways to go about it - either use Google Apps script or use the built-in feature of Google Sheets to remove duplicates.
First, highlight the entire column in Google Sheet that contains the duplicate data. Next, go to the Data menu and choose the Remove Duplicates option.
Select which columns to include and whether or not the selected range has any header row. Click Remove duplicates and your list is clean up in one go. Like with COUNTIF function, Google Sheets will ignore case and formatting when determining duplicates.
Remove Duplicates with Google Scripts
If you prefer automation, here’s a little snippet that will remove the duplicates in your active Google Sheet based on data in the first column.
The Government of India has recently introduced an “open API” for Aarogya Setu, the world’s most popular contact tracing app that has more than 110 million users across the Android and iOS platform. The Aarogya Setu API, in simple English, will help organizations automatically check the health status of their employees.
Currently, when an employee enters his or her office, they are required to show their Aarogya Setu app at the reception and are allowed entry only if the status is green meaning they haven’t been in proximity of an infected person. With the API in places, business can automatically know the risk level of their employees.
This could save some effort since the HR department can create a Google Sheet with the phone numbers of all employees and a Google Script can automatically get the health status of each number in that list. The script can then email the list of employees who are at moderate or high risk for further action.
You can sign-up for the API at openapi.aarogyasetu.gov.in. This isn’t a straightforward process - you have to send an email and approval is granted only if your business has more than 50 employees. Assuming your business has been granted access to the API, here’s how you can use it with Google Sheets and Google Scripts.
classAaryogyaSetu{constructor({ apiKey, userName, password }){this.apiKey = apiKey;this.userName = userName;this.password = password;this.api ="https://api.aarogyasetu.gov.in";this.token =null;}/* Get the authorization token for the header
The token is valid for 1 hour */getToken(){if(this.token ===null){const{ token }=this.fetch("/token",{
username:this.userName,
password:this.password,});this.token = token;}returnthis.token;}/* Request Aarogya Setu status of a
user using phone number of the user */getUserStatus(phone_number){const{ request_id, request_status }=this.fetch("/userstatus",{
phone_number,});return request_status !=="Approved";}fetch(endpoint, payload){const mimeType ="application/json";const headers ={
Accept: mimeType,"Content-Type": mimeType,"x-api-key":this.apiKey,};if(endpoint !=="/token"){
headers["Authorization"]=this.getToken();}const options ={
method:"POST",
contentType: mimeType,
headers: headers,
payload:JSON.stringify(payload),};const url =`${this.api}${endpoint}`;const response = UrlFetchApp.fetch(url, options);returnJSON.parse(response.getContentText());}}/* The API key can be found in your Aarogya Setu dashboard */constmain=()=>{const aarogyasetu =newAaryogyaSetu({
apiKey:"xyz1234",
username:"amit@labnol.org",
password:"India1234",});const phoneNumber ="9760008500";const userStatus = aarogyasetu.getUserStatus(phoneNumber);if(!userStatus){
console.log(`The Aarogya Setu status of ${phoneNumber} was denied`);}};
When you make a request to the Aarogya Setu API requesting the risk status of an employee identified by their phone number, a notification is sent to the Aarogya Setu user. If they approve the status (or if they have pre-approved the request earlier), a POST request is made to your callback URL with the help status of the user.
The Google Script can be published as a web app with the doPost method and that be used as a callback URL for the Open API.
The PayPal website mentions a list of 200 countries where the PayPal service is officially available. There are about 46 countries and regions left where buyers cannot transact using PayPal.
As highlighted in the Google Map above, the regions where PayPal is not available includes Afghanistan, Bangladesh, Cuba, Ghana, Iraq, Iran, North Korea, Lebanon, Liberia, Libya, Pakistan, Palestine, Sudan, Syria, Turkey and Uzbekistan.
If you have a digital goods store that relies exclusively on the PayPal service for processing payments, you could be losing business as customers from countries like Bangladesh, Turkey or Pakistan would not be able to make payments.
As an alternative, you can sign-up for a non-US payment processing service - Paddle and FastSpring are good alternatives - and offer these as payment options on the checkout screen to customers who land on your website from countries where PayPal is unavailable.
Detect the Country of your Website Visitors
I have implemented a similar technique for my Google add-ons website and it seems to work well. The website uses PayPal and Stripe as the default payment handler but if someone lands from a non-supported country, the PayPal buttons are hidden and they are offered an option to checkout with Paddle.
To get the website visitor’s location, I use the ip2c.org service that quickly resolves the visitor’s IP address to their country. If you fetch the ip2c.org/self service, it returns the ISO code of the country of the computer that made the HTTP request.
constgetVisitorCountry=()=>{returnnewPromise((resolve, reject)=>{
window
.fetch("https://ip2c.org/self").then((response)=> response.text()).then((data)=>{const[status, country]=String(data).split(";");if(status !=="1"){thrownewError("Unable to fetch country");}resolve(country);}).catch(()=>{resolve("US");});});};getVisitorCountry().then((country)=>{if(["PK","BD","TR","AF"].indexOf(country)!==-1){// show Paddle Buttons}else{// show PayPal buttons}});
Some online stores follow the “Purchasing Power Parity” theory (learn more) where non-tangible goods like video courses and software licenses are priced dynamically depending on the country of customers. The above client-side approach for detecting the visitor’s location can be help in such scenarios as well.
Google’s web.dev virtual conference happened last month and if there’s one key takeaway from the event, it is the “core web vitals” initiative. Website owners can no longer afford to ignore core vitals as these are now a ranking signal in Google Search.
Google has long been stressing on the need to build faster web pages but with core vitals, they provide a set of actionable metrics - Largest Contentful Paint (LCP), First Input Delay (FID), and Cumulative Layout Shift (CLS) - that should be measured by website owners across mobile and desktop computers.
The session on speed tooling by Elizabeth Sweeny of the Chrome team offers a good overview of what Core Web Vitals are and she also discusses the various tools that are available for webmasters and developers to measure web vitals for their pages.
Measure Core Vitals with Google Sheets
While there are quite a few tools available to help you measure core web vitals for a website - from Chrome extensions to web apps - they have to be triggered manually and can only measure core vitals for a single website / webpage at a time.
If you are looking to automatically measure core web vitals for multiple websites, maybe that of your competitor’s websites as well, here’s a Google Sheet that can help. The spreadsheet will not only help you measure vitals for multiple URLs but you can also visualize the change in various metrics over time with the help of sparklines.
Here’s how you can get started:
Click here to make a copy of the Web Vitals sheet in your Google Drive.
Switch to the first tab and enter a list of URLs that you would like to measure and track. Also provide unique sheet names for each URL and the metrics for that URL will get stored in the corresponding sheet.
Click the “Track Core Vitals” button, authorize the sheet and you’re all set.
The button will set up a daily cron job that will automatically connect to Google’s PageSpeed API, fetch the core vitals for each specified URL and write them to the corresponding sheet.
The Apps Script source code is available on Github. As always, you are free to reuse, modify and publish the code based on your own requirements.
If I were to pick a YouTube video with the most accurate and most up-to-date title, this video by Tom Scott will probably take the top spot. The title of the video says “This video has n views’ and this title updates automatically as the number of views change over time.
Little wonder, the world of YouTube is fascinated with this “magic” title and the video has garnered over 20 million views so far.
If you are left wondering how this is done, here’s the secret sauce - the YouTube API. We create a background cron job that runs, say, every 5 minutes and gets the current number of views for the specified video. If the number of views has increased since the last run, we update the video title with, you got it right, the YouTube API.
Make your own “This Video has n Views” title
Like to build something similar for a video on your own YouTube channel? Well, there’s always Google Apps Script to the rescue.
Make a copy of this Google Script in your Google Drive.
Replace <<VIDEO ID>> with the video id of the YouTube video that you would like to use for this experiment. If the video URL is youtube.com/watch?v=abc, the video id is abc.
Go to the Run menu inside the Apps Script editor, choose Run and select updateYouTubeVideo. Allow the script to manage your YouTube account and that’s it.
The script will run every five minutes and update the title of your YouTube video auto-magically. Simple!
constupdateYouTubeVideo=(e =null)=>{const id ='<<VIDEO ID>>';const template ='This video has VIEWCOUNT views and COMMENTCOUNT comments';// The cron job is created only when the script is run manuallyif(e ===null){const triggerName ='updateYouTubeVideo';const triggers = ScriptApp.getProjectTriggers().filter((trigger)=>{return trigger.getHandlerFunction()=== triggerName;});// If time based trigger doesn't exist, create one that runs every 5 minutesif(triggers.length ===0){
ScriptApp.newTrigger(triggerName).timeBased().everyMinutes(5).create();}}// Get the watch statistics of the videoconst{ items:[video ={}]=[]}= YouTube.Videos.list('snippet,statistics',{ id });// Parse the YouTube API response to get views and comment countconst{
snippet:{ title: oldTitle, categoryId }={},
statistics:{ viewCount, commentCount }={},}= video;if(viewCount && commentCount){const newTitle = template
.replace('VIEWCOUNT', viewCount).replace('COMMENTCOUNT', commentCount);// If the video title has not changed, skip this stepif(oldTitle !== newTitle){
YouTube.Videos.update({ id, snippet:{ title: newTitle, categoryId }},'snippet');}}};
How to Stop the YouTube Google Script
Go to script.google.com and search for the YouTube script in the My Projects dashboard. Go to the triggers menu and remove the trigger. The script will stop updating the video title in the background.
You can also find this Google Script on my Github page.
