Month: March 2021

How do I contact the owner of a website? Where is a particular website hosted? What other websites are hosted on that same server? Is the site using WordPress or Gatsby? Which ad networks are they using to monetize a site? Is my site accessible from China?

Here are some of the most useful online tools that will help you know every single detail of any website.

Also see: The Essentials Tools for Programmers

• just-ping.com — Use Just Ping to determine if a particular website is accessible from other countries. Just Ping has monitoring servers across the world including Turkey, Egypt, and China so if the ping results say 100% Packet Loss, most likely the site is inaccessible from that region.
• who.is — If you like to know the contact address, email and phone number of the website owner, this free whois lookup service will help. This is a universal lookup service meaning it can simultaneously query the whois database of all popular domain registrars.
• whoishostingthis.com — Enter the URL of any website and this online service will show you the name of the company where that website is hosted. This may come handy if you need the contact information of the web hosting provider for writing a DMCA Notice or if you are looking to switch web hosts.
• chillingeffects.org — When there’s a copyright-related complaint against a website, a copy of that letter is archived in the Chilling Effects database. Anyone can query this public database to know about all the copyright infringement complaints against a particular website.
• myip.ms — MyIP.ms offers a comprehensive report of any website or I.P. Address. You get to know about the hosting provider, the physical location of a website, the IP Address change history of a website and the DNS information. Netcraft also offers similar reports.
• reversewhois.com — The reverse whois lookup will help you determine other websites of someone. You can search the whois database by the email address or name of the domain registrant.
• builtwith.com — Use BuiltWith to know the technology stack of any website. It helps you figure out the mail service provider of a domain, the advertising partners, the tracking widgets that are installed on a website and whether the site is using any CDN like Amazon S3 or Google Cloud. See example.
• ssllabs.com - The certificate diagnostics tool will verify your site’s SSL certificate and ensure that it is correctly installed, trusted and does not show errors to any of your site visitors.
• semrush.com — If you wish to analyze your competitor’s website, this is the tool to go with. SEM Rush will help you figure what organic keywords are people using to find a website, what is the site’s traffic and which are the competing websites.
• dnsmap.io — When you buy a new domain or switch from one host to another, the DNS records for the domain changes and it may take a while to propagate these changes worldwide. The tool checks the DNS records from various geographic locations and it can check your domain’s A, CNAME, TXT and MX records. whatsmydns.net is also a good alternative.
• toolbox.googleapps.com — If email messages, including those sent via Mail Merge, from your domain are not reaching the recipient’s mailbox, use this Google tool to confirm that DMARC, DKIM and SPF records are properly configured for your domain.
• browserstack.com - Check your website’s responsive design on multiple desktops, tables, iOS and Android phones running different versions of operating systems.
• screenshot.guru - If a website is inaccessible, use Screenshot Guru, hosted on the Google Cloud, to confirm if the website is down or not.
• thinkwithgoogle.com - A mobile speed tool developed by Google that will help you determine how fast your websites will load on mobile phones on 3G and 4G network. You can also compare your mobile speed score with other websites.
• testmysite.io - A simple site testing tool from Netlify that will measure and rank your site’s loading time from different regions around the world.
• developers.google.com — Find the Page Speed score of any website on both desktop and mobile devices. The higher this number, the better. The Google tool also offers suggestions on how the score can be improved.
• httparchive.org — The HTTP Archive is a repository of all performance-related metrics for a website. It keeps a record of the size of pages, their average load time and the number of failed requests (missing resources) over time.
• Website Monitor - Use this open-source Google Sheets based website monitoring tool to get alerts when your domain goes down or is inaccessible.
• Flush DNS - Use this tool to flush the Google DNS cache for a domain. If you changed the DNS servers for your domain, by changing registrars or DNS hosting in the last few days, flush your main domain name first before you flush any subdomains. OpenDNS also has a web tool for refreshing the DNS cache.
• DomainTools - The tool monitors one or more web domains and sends email alerts when the domain is expiring, the domain gets renewed, the nameservers change or when the registrant information is updated for the domain.

Also see: 101 Most Useful Websites

You are working inside a Google Spreadsheet where a formula needs to copied down to the last row of the sheet. You also need the formula to be added automatically when a new row is added to the Google Sheet.

There are several ways to solve this problem.

Copy Formula Down in Google Sheets

The easiest approach to copy down formulas is to use the fill handle in Google Sheets. Write your formula in the first row of your spreadsheet, and then point your mouse to the lower right corner of the formula cell.

The pointer changes into a fill handle (black plus symbol) that you can drag to the last row of the sheet. The fill handle will not just copy down the formulas to all the adjacent cells but also copies the visual formatting.

If you need to copy the formulas across cells but sans any formatting, select the cell that contains the formatting and press Ctrl+C to copy it to the clipboard. Next, select the range where that formula needs to applied, right-click, choose Paste Special and Paste Formula only.

Apply Formula to the Entire Column in Google Sheets

If you have hundreds of rows in a Google Spreadsheet and you want to apply the same formula to all rows of a particular column, there’s a more efficient solution than copy-paste - Array Formulas.

Highlight the first cell in the column and type the formula as earlier. However, instead of specifying a single cell as a parameter, we’ll specify the entire column using the B2:B notation (start from cell B2 and go all the way down to the last row of column B).

Then press Ctrl+Shift+Enter, or Cmd+Shift+Enter on Mac, and Google Sheets will automatically surround your formula with ARRAYFORMULA function.

Thus, we could apply the formula to the entire column of the spreadsheet with only a single cell. Array Formulas are more efficient as they process a batch of rows in one go. They are also easier to maintain as you only need to modify a single cell to edit the formula.

One issue that you may have noticed with the above formulae is that it applies to every row in the column where you have only want to add formulas to rows that contain data and skip the blank rows.

This can be done by adding an IF contain to our ARRAYFORMULA so that it doesn’t apply the formula the any of the blank rows.

Google Spreadsheet offers two functions to help test whether a cell is empty or now.

• ISBLANK(A1) - Returns TRUE if the referenced cell is empty.
• LEN(A1) <> 0 - Returns TRUE if the referenced cell not empty, FALSE otherwise

Our modified Array Formulas would therefore read:

Using ISBLANK(Cell Reference):

There are several other ways to test if a cell is blank or not:

=ArrayFormula(IF(ISBLANK(B2:B), "", ROUND(B2:B*18%, 2)))
=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), ""))
=ArrayFormula(IF(B2:B="", "", ROUND(B2:B*18%, 2)))

Use Array Formulas inside Column Headers

In our previous examples, the text of the column titles (like Tax, Total Amount) was pre-populated and the formulas were only added to the first row of the dataset.

We can further improve our formula so that they can be applied to the column header itself. If the index of the current row is 1, calculated using the ROW() function, the formula outputs the column title else it performs the calculation using the formula.

=ArrayFormula(IF(ROW(B:B)=1,"Tax",IF(ISBLANK(B:B),"",ROUND(B:B*18%, 2))))

Auto Fill Formulas into Google Form Submissions

ARRAYFORMULA functions are particularly useful for Google Forms when the form responses are getting saved inside a Google Sheet. You cannot do live calculations inside Google Forms but they can be performed inside the spreadsheet that is collecting the responses.

You can create new columns inside the Google Spreadsheet and apply the ARRAYFORMULA to the first row of the added columns.

When a new form submission is received, a new row would be added to the Google Sheet and the formulas would be cloned and automatically applied to the new rows without you have to copy-paste stuff.

Also see: Convert Google Form Response to PDF Documents

How to Use VLOOKUP inside ARRAYFORMULA

You can combine ARRAYFORMULA with VLOOKUP to quickly perform a lookup across an entire column.

Say you have a “Fruits” sheet that lists the fruit names in column A and the corresponding prices in column B. The second sheet “Orders” has fruit names in column A, the quantity in column B and you are supposed to calculate the order amount in column C.

=ArrayFormula(
  IF(ROW(A:A)=1,
  "Total",
  IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))

In simple English, if the row of the current cell is 1, output the column title in plain text. If the row is greater than 1 and the column A of the current row is not empty, perform a VLOOKUP to fetch the price of the item from the Fruits sheet. Then multiply that price with the quantity in cell B and output the value in cell C.

If your VLOOKUP range is in another Google Spreadsheet, use the IMPORTRANGE() function with the ID of the other Google Sheet.

Please note that you may have to use semicolons in the spreadsheet formulas instead of commas for some locales.