Photo by Daniel Irwin on Unsplash

Fair Warning: This is a spreadsheet-based method to calculate the purchase value of shares or inventory using the FIFO, first-in-first-out method. You will NOT be able to calculate profit or loss using this.

First-in-first-out or FIFO is a method of inventory management. In this method, when you sell a unit, this sale is adjusted against the first unit purchased.

Another method is LIFO or last-in-first-out where the last unit bought is considered sold first.

The objective is to figure out the value of the unsold units. And to be able to identify which units (i.e. …


Photo of a dog by Lucrezia Carnelos on Unsplash
Photo of a dog by Lucrezia Carnelos on Unsplash
Photo by Lucrezia Carnelos on Unsplash

Use batch operations

If you are reading data from a Google Sheet — or writing data to it — you should not loop through the cells. Instead, you should use batch operations. (See this best practices guide for Google Apps Script.)

Writing a defined array to a sheet

To write multiple rows and columns of data to a sheet, you need to set up the data in an array. Here is an array of pets and their owners:

var array = [['Asterix', 'Tintin', 'Goofy'],
['Dogmatix', 'Snowy', 'Pluto']];

The first sub-array has the name of the owners and this will go in row one. …


Photo by Alex Nicolopoulos on Unsplash

Google Sheets has a powerful find and replace function. But you may want to use Google Apps Script to do the same job.

For example, you get a new spreadsheet every day and have to find “Tom” and replace it with “Scratchy”. A script can get the job done in a click.

StackOverflow has a question on this. The accepted answer would require you to loop through all cells, find the text, replace it and post the new text back to the cell.

Google Apps Script has a more powerful, and simpler, method called TextFinder.

With TextFinder you don’t need…


There is a PDF file on the Internet. Say this one (The Google COVID-19 Community Mobility Report for the US of A).

And you want to save it to your Google Drive.

The tedious way is to download it to your computer. … and then upload it to your Google Drive. (Seems like too much work.)

If only you could send the PDF straight from where it is stored on the Internet, to your Google Drive.

Get the file

Get the URL and set it to a variable:

var url = ‘https://www.gstatic.com/covid19/mobility/2021-02-21_US_Mobility_Report_en.pdf';

Then get the PDF as a blob:

var blob = UrlFetchApp.fetch(url).getBlob();


This question was asked and answered on StackOverflow.

I have simplified the solution suggested by “The Master”.

In this example, I am going to use a Google Apps Script attached to a Google Sheet.

And I will use a menu item to trigger the script to open the URL.

Step 1: Add a menu to the Google Sheets UI to trigger the script:

function onOpen() {   var ui = SpreadsheetApp.getUi();   ui.createMenu(‘📮 Open Gmail’)     .addItem(‘Open Gmail’, ‘openGmail’)     .addToUi();}

This part of the script will add a menu item.


Data Validation: to control what a user can enter into a cell

It’s hard to set-up dependent data validation using just formulas. Prasant has a nice tutorial on how to do this with formulas.

This is a quick tutorial on how to do this using Google Apps Script.

This tutorial is in response to this question on StackOverflow. The sheet needs to validate data based on countries. If the user keys in a country name, the adjacent cell should provide a drop-down list of the cities from that country as listed in the country-city table.


Kaspersky Blog, “Passwords are like underwear”

Your passwords suck at security because no one taught you how to choose a password. Here’s what you need to teach your kids about passwords.

If you are not a cryptographic nerd, you have two types of passwords:

(a) Memorable passwords. This is usually just one password that you can easily remember. And you use it on 53 websites and 17 apps. It is something simple like “qwerty” or “password” or “123456”.

Check if you are using a common password here.

(b) Complicated passwords that you think are safe. …

ADW

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store