CodeCanyon

Reddit offers a fairly extensive API that any developer can use to easily pull data from subreddits. You can fetch posts, user comments, image thumbnails, votes and most other attributes that are attached to a post on Reddit. The only downside with the Reddit API is that it will not provide any historical data and your requests are capped to the 1000 most recent posts published on a subreddit. So, for instance, if your project requires you to scrape all mentions of your brand ever made on Reddit, the official API will be of little help. You have tools like wget that can quickly download entire websites for offline use but they are mostly useless for scraping Reddit data since the site doesn’t use page numbers and content of pages is constantly changing. A post can be listed on the first page of a subreddit but it could be pushed to the third page the next second as other posts are voted to the top. Download Reddit Data with Google Scripts While there exist quite a Node.js and Python libraries for scraping Reddit, they are too complicated to implement for the non-techie crowd. Fortunately, there’s always Google Apps Script to the rescue. Here’s Google script that will help you download all the user posts from any subreddit on Reddit to a Google Sheet. And because we are using pushshift.io instead of the official Reddit API, we are no longer capped to the first 1000 posts. It will download everything that’s every posted on a subreddit. To get started, open the Google Sheet and make a copy in your Google Drive. Go to Tools -> Script editor to open the Google Script that will fetch all the data from the specified subreddit. Go to line 55 and change technology to the name of the subreddit that you wish to scrape. While you are in the script editor, choose Run -> scrapeReddit. Authorize the script and within a minute or two, all the Reddit posts will be added to your Google Sheet. Technical Details - How to the Script Works The first step is to ensure that the script not hitting any rate limits of the PushShift service. const isRateLimited = () => { const response = UrlFetchApp.fetch('https://api.pushshift.io/meta'); const { server_ratelimit_per_minute: limit } = JSON.parse(response); return limit < 1; }; Next, we specify the subreddit name and run our script to fetch posts in batches of 1000 each. Once a batch is complete, we write the data to a Google Sheet. const getAPIEndpoint_ = (subreddit, before = '') => { const fields = ['title', 'created_utc', 'url', 'thumbnail', 'full_link']; const size = 1000; const base = 'https://api.pushshift.io/reddit/search/submission'; const params = { subreddit, size, fields: fields.join(',') }; if (before) params.before = before; const query = Object.keys(params) .map((key) => `${key}=${params[key]}`) .join('&'); return `${base}?${query}`; }; const scrapeReddit = (subreddit = 'technology') => { let before = ''; do { const apiUrl = getAPIEndpoint_(subreddit, before); const response = UrlFetchApp.fetch(apiUrl); const { data } = JSON.parse(response); const { length } = data; before = length > 0 ? String(data[length - 1].created_utc) : ''; if (length > 0) { writeDataToSheets_(data); } } while (before !== '' && !isRateLimited()); }; The default response from Push Shift service contains a lot of fields, we are thus using the fields parameter to only request the relevant data like post title, post link, date created and so on. If the response contains a thumbnail image, we convert that into a Google Sheets function so you can preview the image inside the sheet itself. The same is done for URLs. const getThumbnailLink_ = (url) => { if (!/^http/.test(url)) return ''; return `=IMAGE("${url}")`; }; const getHyperlink_ = (url, text) => { if (!/^http/.test(url)) return ''; return `=HYPERLINK("${url}", "${text}")`; }; Bonus Tip: Every search page and subreddit on Reddit can be converted into JSON format using a simple URL hack. Just append .json to the Reddit URL and you have a JSON response. For instance, if the URL is https://www.reddit.com/r/todayIlearned, the same page can be accessed in JSON format using the URL https://www.reddit.com/r/todayIlearned.json. This works for search results as well. The search page for https://www.reddit.com/search/?q=india can be downloaded as JSON using https://www.reddit.com/search.json?q=india. View the full article

The Gmail Mail Merge addon can now import the email addresses of subscribers from your MailChimp mailing lists into Google Sheets. If you wish to send emails to your subscribers directly from Gmail, instead of using MailChimp mail servers, this is the way to go. As a developer, you can use Google Apps Script to import subscriber lists, HTML campaigns, performance reports and any other data from MailChimp to Google Sheets for analysis. You can use the MailChimp OAuth2 library but in this example, we’ll use the developer key directly to connect to MailChimp. Get the MailChimp Developer Key In your Mailchimp account, navigate to the Account page. In the drop-down menu, select Extras, and then API keys. Click Create A Key and make a note of it. Google Apps Script - Get MailChimp Audiences const MAILCHIMP_API_KEY = '<>'; // MailChimp API key includes the data center id // that your MailChimp account is associated with const makeHttpRequest = (endpoint, params = {}) => { const [, mailchimpDataCenter] = MAILCHIMP_API_KEY.split('-'); const url = `https://${mailchimpDataCenter}.api.mailchimp.com/3.0/${endpoint}`; const qs = Object.keys(params) .map((key) => `${key}=${params[key]}`) .join('&'); const apiUrl = qs ? `${url}?${qs}` : url; const request = UrlFetchApp.fetch(apiUrl, { method: 'GET', headers: { Authorization: `Basic ${Utilities.base64Encode(`labnol:${MAILCHIMP_API_KEY}`)}`, }, }); return JSON.parse(request); }; const getListMembers = (id, offset) => { const { members } = makeHttpRequest(`lists/${id}/members`, { count: 100, offset, fields: 'members.email_address', status: 'subscribed', sort_field: 'last_changed', sort_dir: 'DESC', }); return members.map(({ email_address: email }) => [email]); }; // Get a list of all subscribers of a specific // MailChimp mailing list, you can retrieve the email address, // name and subscription statues of subscribers const getMailChimpListMembers = (id) => { let hasMore = true; let data = []; do { const emails = getListMembers(id, data.length); data = [...data, ...emails]; hasMore = emails.length > 0; } while (hasMore); return data; }; // Get a list of all audiences / lists from MailChimp const getMailChimpLists = () => { const params = { count: 10, fields: 'lists.id,lists.name', sort_field: 'date_created', sort_dir: 'DESC', }; const { lists = [] } = makeHttpRequest('lists', params); return lists.map(({ id, name }) => ({ id, name, members: getMailChimpListMembers(id), })); }; The GetMailChimpLists method will bring all the lists and associated email addresses in a JSON object that you can easily write to Google Sheets using the SpreadsheetApp service. View the full article

The Government of India website has a live dashboard that provides, in near real-time, the number of Coronavirus (COVID-19) cases in various states of India. This is the best resource to get updates around active COVID-19 cases in India. COVID-19 Tracker for India The official website provides the current data but if you were to check how the number of confirmed cases increased in India over time, there’s no historic data available. That’s one reason I built the COVID-19 Tracker with Google Sheets. The tracker scrapes data from the official website every few minutes and uses Sparklines to help you visualize how the coronavirus outbreak is spreading in India over time. The Government has been actively publishing reports since March 10 and all the data can also be accessed through the Google Sheet. ️COVID-19 Sheets Tracker COVID-19 JSON API If you are a developer, I’ve also published the data as a JSON API that will provide you the latest state-wise data of COVID-19 cases as available on the Ministry of Health and Family Welfare website of India. How the COVID-19 Tracker Works The Coronavirus Tracker is written in Google Apps Script and it uses time-based triggers to scrape numbers from the mohfw.gov.in website every few minutes. /** * Scrape the homepage of mohfw.gov.in (Ministry of Health, India) * website for latest numbers on Coronavirus positive cases in India */ const scrapeMOHWebsite = () => { const url = "https://www.mohfw.gov.in/"; const response = UrlFetchApp.fetch(url); const content = response.getContentText(); return content.replace(/[\r\n]/g, ""); }; Google Apps Script doesn’t support HTML parsers like Cheerio so we had to quickly build one from scratch using regex. It grabs the HTML content of the page, looks for the table tag and then extracts data from individual cells of the table. If they change the layout of the website, this parser is likely to break. /** * Parse the webpage content and extract numbers from the HTML * table that contains statewise data on Covid-19 Cases in India */ const getCurrentCovid19Cases = (json = true) => { const states = {}; const html = scrapeMOHWebsite(); const [table] = html.match(/.+?>(.+)<\/div>/); const rows = table.match(/(.+?)<\/tr>/g); rows.forEach(row => { const cells = row.match(/.+?>(.+?)<\/td>/g).map(cell => cell.replace(/<.+?>/g, "")); const [, stateName, indianNationals, foreignNationals] = cells; if (/[a-z\s]/i.test(stateName)) { states[stateName] = Number(indianNationals) + Number(foreignNationals); } }); return json ? states : JSON.stringify(states); }; Once we have the data in JSON format, we can easily write to a Google Spreadsheet using Apps Script. The script adds a new column per day while retaining the old data for comparison. /** * Write the parsed data into a new column in Google Sheet * All the historic data is also preserved in the sheet. */ const writeNewCovid19CasesToSheets = covid19Cases => { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard"); const states = sheet .getRange(3, 1, sheet.getLastRow() - 2, 1) .getValues() .map(([state]) => [covid19Cases[state] || 0]); sheet .getRange(2, sheet.getLastColumn() + 1, states.length + 1, 1) .setValues([[new Date()], ...states.map(count => [count])]); }; The COVID-19 tracker in Google Sheets also provides a JSON API that you can use to import data directly in your apps and websites. To publish a JSON API, we have published the script as a web app with the doGet callback function. The ContentService service returns the raw JSON output whenever an external app invokes the Google script URL. const doGet = () => { const key = "Covid19India"; const cache = CacheService.getScriptCache(); let data = cache.get(key); if (data === null) { data = getCurrentCovid19Cases(false); cache.put(key, data, 21600); } return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.JSON); }; All the code is open-source and you are free to use in any project. View the full article

Websites can determine the visitor’s geographic location using their IP address and serve more relevant content. For example, a weather website may use your IP address to estimate your approximate location and provide weather forecast for your current city automatically. A currency exchange website can determine your default currency based on your country which is detected from your IP address. There are free web IP lookup services, ip2c.org for example, that will reveal the country of your client’s IP address with a simple HTTP request. We internally use that service at Digital Inspiration to determine the payment service provider on the checkout page. Bulk IP Lookup with Google Sheets IP2Location is another good alternative that retrieves more detailed geolocation information for any IP address. The IP location lookup service can retrieve the client’s country, city name, region, the ISP name and more. If you have a bulk list of IP addresses, you can use Google Sheets to estimate the corresponding geographic details for each of the addresses in few easy steps: Click here to make a copy of the Google Sheet for performing IP lookups in bulk. Paste the list of IP addresses in column A, one per row. The lookup service works for both IPv4 and IPv6 addresses. Enter your key in cell E1. If you have a small list of IP address, use demo as the key or get your own API key from ip2location.com. Click the Run button, authorize the script and watch as the geographic details and ISP names are populated in the sheet. How IP2Location Script Works Internally, the Google Sheet uses the IP2location web service with Google Apps Script to transform IP addresses into geographic region. It uses the UrlFetchApp service to perform multiple HTTP requests in a single batch for improved performance. Here’s the full source code: const ip2location = () => { // Get all the input data from Google Sheet const ss = SpreadsheetApp.getActiveSheet(); const data = ss.getDataRange().getDisplayValues(); // Use your own API key or use demo key const apiKey = data[0][4] || 'demo'; // Generate API URL for IP address const getUri_ = (ipAddress) => { const API_URL = 'https://api.ip2location.com/v2'; return `${API_URL}/?ip=${ipAddress}&key=${apiKey}&package=ws4`; }; const requests = []; for (let r = 2; r < data.length; r++) { const [ipAddress, countryName] = data[r]; // Only process rows where the country is blank if (ipAddress && !countryName) { requests.push({ url: getUri_(ipAddress), rowNumber: r + 1 }); } } // Make API calls in bulk using the UrlFetchApp service UrlFetchApp.fetchAll(requests).forEach((content, i) => { // Parse the JSON response const { city_name, country_name, isp, response } = JSON.parse(content); // If the response is populated, the API call failed if (response) throw new Error(response); // Write the response data to Google Sheet const values = [[country_name, region_name, city_name, isp]]; ss.getRange(requests[i].rowNumber, 2, 1, 4).setValues(values); }); // Flush all changes SpreadsheetApp.flush(); }; View the full article

An international school is building a Google Form where students can register their details. The form would have a drop-down list of countries, the names of class teachers as a multiple choice question and a checkbox style question where students can pick one or more of their favorite subjects. Adding Bulk Questions in Google Forms It is easy to create such a form inside Google Forms - here’s a sample form - but there are two issues: There’s too much data to enter and type in the form. For instance, the country down-down alone has a list of 250 countries and it would take some work for the form editor to manually type each choice in the question. The question choices in the form may change with time. In the previous example, some teachers may move out, new teachers may join in and the drop-down list in the form has to be updated manually every time there’s a change in the staff. Auto-Populate Questions in Google Forms with Google Sheets As with everything else in the world of Google Forms and Google Sheets, we can easily automate the process of adding question choices in Google Forms in bulk with the help of, you guessed it right, Google Apps Script. Play ; The idea is simple. We’ll have a Google Sheet that will be the data source and have all the answer choices for various questions in the Google Form. The app will read the data from this Google Sheet and auto-populate the choices in the form with one click. You can even create a time-trigger that runs every hour, day or month to dynamically update your form using the most current data available in the spreadsheet. Add Options in Dropdown Lists & Multiple Choice Questions Create a Google Spreadsheet and add the question titles in the first row of the sheet, one per column. Next, write down all the options or choices that should be available per question. Here’s how your spreadsheet structure would look like: The important thing to note here is that your column headings in the spreadsheet should exactly match the form field labels of the Google Form. The Google Script can bulk add answers in multiple-choice questions with a single answer, drop-down lists and checkbox with multiple options. Bulk Add Question Choices in Google Forms Open the Google Sheet that has the question choices, go to the Tools menu and choose Script Editor. Replace the default code in the script editor with the Google Script below. Please watch the video tutorial to know in more detail how this code works. /** * Auto-populate Question options in Google Forms * from values in Google Spreadsheet * * Written by Amit Agarwal (MIT License) * **/ const populateGoogleForms = () => { const GOOGLE_SHEET_NAME = "<>"; const GOOGLE_FORM_ID = "<>"; const ss = SpreadsheetApp.getActiveSpreadsheet(); const [header, ...data] = ss.getSheetByName(GOOGLE_SHEET_NAME).getDataRange().getDisplayValues(); const choices = {}; header.forEach((title, i) => { choices[title] = data.map(d => d[i]).filter(e => e); }); FormApp.openById(GOOGLE_FORM_ID) .getItems() .map(item => ({ item, values: choices[item.getTitle()], })) .filter(({ values }) => values) .forEach(({ item, values }) => { switch (item.getType()) { case FormApp.ItemType.CHECKBOX: item.asCheckboxItem().setChoiceValues(values); break; case FormApp.ItemType.LIST: item.asListItem().setChoiceValues(values); break; case FormApp.ItemType.MULTIPLE_CHOICE: item.asMultipleChoiceItem().setChoiceValues(values); break; default: // ignore item } }); ss.toast("Google Form Updated !!"); }; You need to replace the GOOGLE_SHEET_NAME and the GOOGLE_FORM_ID with your own values. Go to the Run menu inside the Script editor, choose populateGoogleForms function and it should instantly choices for all the specified questions in the Google Form. Dynamically Update Answers in Google Forms Going forward, whenever you need to update the answer choices in Google Forms, you can simply update the values inside the spreadsheet and run the same auto-populate function from the Script editor. Or, to make things even more simple, you can add a button on the spreadsheet page that, when clicked, will automatically update the answers in Google Forms for you. Go to the Insert menu in Google Sheets, choose drawing and pick any shape. You can also add overlay text to the shape. Once the shape is placed on the spreadsheet canvas, click the menu, choose assign script and type populateGoogleForms. That’s it. You can click this button to update your Google Forms right within Google sheets. Do note that the script will not append choices, it will replace all existing choices with the ones that are available in your Google Sheet. Also see: Send Pre-filled Google Forms via Email View the full article

A small business maintains their staff roster in a simple Google Sheet - the column A of the sheet contains a list of all employee names and column B contains a list of employees who have been assigned to a project. The immediate task is to identify staff members who are part of the organization but have not been assigned any project yet. In other words, the manager needs to figure out all employee names from column A who are not preset in column B. There are two ways to solve this problem - visually and through formulas. Using Visual Formatting The first option would be to highlight cells in column A that are missing in column B. Inside the Google Sheet, go to the Format menu and choose conditional formatting. Here select A2:A for the range field, choose Custom Formula from the Format Cells If dropdown and paste the formula: =COUNTIF(B$2:B, A2)=0 The COUNTIF function will essentially count the occurrence of each item in Column A against the range of cells in Column B. If the count for an item in Column A is 0, it means that the cell value is not present in column B and the cell is highlighted with a different background color. Find Missing Items in Another Column The next approach uses Google Sheet formulas to create a list of items that are in Column A but missing in Column B. We’ll make use of the FILTER function that, as the name suggests, returns only a filtered version of a range that meets a specific criteria. In our case, the criteria is similar to the one that we used in the visual formatting section. Go to column C (or any blank column) and enter this formula in the first empty cell. =FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0))) The MATCH function returns the position of items in Column A in the range associated with Column B and it returns #N/A if the values is not found. When the result is used with ISNA, it returns true only when the match is not found. Using Google Query Language SQL geeks may also use the Google Query Language, we are used it with D3.js visualization, to print the list of names that are in Column B but not in Column B. =QUERY(A2:A, "SELECT A WHERE A <> '' AND NOT A MATCHES '"&TEXTJOIN("|",TRUE,B2:B)&"' ORDER BY A") The matches operator in the where clause does a regex comparison and the order by clause in the query will automatically sort the output alphabetically. View the full article

Google Sheets includes built-in functions for converting cell references in A1 notation to row and column numbers and another function for converting column alphabets (like AA) into the column index (26 in this case). =ADDRESS(23, 28, 4) - Returns the A1 style notation of the cell whose row number is 23 and column number is 28. =COLUMN(C9) - Returns the column number of a specified cell C9 where column A corresponds to 1 and column AA corresponds to 27. Get A1 Notation with JavaScript If you are working with the Google Sheets API, you may sometimes needs to calculate the A1 notation style reference of a cell whose row and column numbers are known in the JSON data of the sheet. For container bound Google Sheets, the getA1Notation() method can return the range address in A1 Notation. const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange(1, 2); Logger.log(range.getA1Notation()); If you are not using the Spreadsheet service, you can also compute the A1 notation reference of a cell using simple JavaScript. /** * * @param {number} row - The row number of the cell reference. Row 1 is row number 0. * @param {number} column - The column number of the cell reference. A is column number 0. * @returns {string} Returns a cell reference as a string using A1 Notation * * @example * * getA1Notation(2, 4) returns "E3" * getA1Notation(2, 4) returns "E3" * */ const getA1Notation = (row, column) => { const a1Notation = [`${row + 1}`]; const totalAlphabets = 'Z'.charCodeAt() - 'A'.charCodeAt() + 1; let block = column; while (block >= 0) { a1Notation.unshift(String.fromCharCode((block % totalAlphabets) + 'A'.charCodeAt())); block = Math.floor(block / totalAlphabets) - 1; } return a1Notation.join(''); }; This is equivalent to =ADDRESS() function of Google Sheets. Get Column Number from A1 Notation The next function takes the cell reference in A1 notation and returns the column number and row number of any cell in the spreadsheet. /** * * @param {string} cell - The cell address in A1 notation * @returns {object} The row number and column number of the cell (0-based) * * @example * * fromA1Notation("A2") returns {row: 1, column: 3} * */ const fromA1Notation = (cell) => { const [, columnName, row] = cell.toUpperCase().match(/([A-Z]+)([0-9]+)/); const characters = 'Z'.charCodeAt() - 'A'.charCodeAt() + 1; let column = 0; columnName.split('').forEach((char) => { column *= characters; column += char.charCodeAt() - 'A'.charCodeAt() + 1; }); return { row, column }; }; This is equivalent to the =ROW() and =COLUMN() functions available in Google Sheets. View the full article

The REMOVE_ACCENTED function for Google Sheets will replace all accented characters in the referenced cell, like the letters è, õ, ā, ĝ and so on with their normal Latin equivalents. To get started, make a copy of the Google Sheet, go to the Tools menu, choose Script Editor and copy the entire code to your clipboard. Now open your own Google Sheet and paste the same code inside the Script editor of your sheet. Save and you should be able to use the REMOVE_ACCENTED function in your own sheets. Input StringOutput stringA História de Malú e João MiguelA Historia de Malu e Joao MiguelSímbolo de su unidad y permanenciaSimbolo de su unidad y permanenciaTomás Gutiérrez AleaTomas Gutierrez AleaMiguel Ángel Félix GallardoMiguel Angel Felix GallardoInternally, this function uses the deburr function of the popular lodash library that converts Latin-1 Supplement and Latin Extended-A letters to basic Latin letters and also removes any combining diacritical marks. Find and Replace Accented Letters in Spreadsheets const latinRegEx = /[\xc0-\xd6\xd8-\xf6\xf8-\xff\u0100-\u017f]/g; const comboRegEx = `[\\u0300-\\u036f\\ufe20-\\ufe2f\\u20d0-\\u20ff]`; /** Used to map Latin Unicode letters to basic Latin letters. */ const latinUnicodeLetters = { // Latin-1 Supplement block. '\xc0': 'A', '\xc1': 'A', '\xc2': 'A', '\xc3': 'A', '\xc4': 'A', '\xc5': 'A', '\xe0': 'a', '\xe1': 'a', '\xe2': 'a', '\xe3': 'a', '\xe4': 'a', '\xe5': 'a', '\xc7': 'C', '\xe7': 'c', '\xd0': 'D', '\xf0': 'd', '\xc8': 'E', '\xc9': 'E', '\xca': 'E', '\xcb': 'E', '\xe8': 'e', '\xe9': 'e', '\xea': 'e', '\xeb': 'e', '\xcc': 'I', '\xcd': 'I', '\xce': 'I', '\xcf': 'I', '\xec': 'i', '\xed': 'i', '\xee': 'i', '\xef': 'i', '\xd1': 'N', '\xf1': 'n', '\xd2': 'O', '\xd3': 'O', '\xd4': 'O', '\xd5': 'O', '\xd6': 'O', '\xd8': 'O', '\xf2': 'o', '\xf3': 'o', '\xf4': 'o', '\xf5': 'o', '\xf6': 'o', '\xf8': 'o', '\xd9': 'U', '\xda': 'U', '\xdb': 'U', '\xdc': 'U', '\xf9': 'u', '\xfa': 'u', '\xfb': 'u', '\xfc': 'u', '\xdd': 'Y', '\xfd': 'y', '\xff': 'y', '\xc6': 'Ae', '\xe6': 'ae', '\xde': 'Th', '\xfe': 'th', '\xdf': 'ss', // Latin Extended-A block. '\u0100': 'A', '\u0102': 'A', '\u0104': 'A', '\u0101': 'a', '\u0103': 'a', '\u0105': 'a', '\u0106': 'C', '\u0108': 'C', '\u010a': 'C', '\u010c': 'C', '\u0107': 'c', '\u0109': 'c', '\u010b': 'c', '\u010d': 'c', '\u010e': 'D', '\u0110': 'D', '\u010f': 'd', '\u0111': 'd', '\u0112': 'E', '\u0114': 'E', '\u0116': 'E', '\u0118': 'E', '\u011a': 'E', '\u0113': 'e', '\u0115': 'e', '\u0117': 'e', '\u0119': 'e', '\u011b': 'e', '\u011c': 'G', '\u011e': 'G', '\u0120': 'G', '\u0122': 'G', '\u011d': 'g', '\u011f': 'g', '\u0121': 'g', '\u0123': 'g', '\u0124': 'H', '\u0126': 'H', '\u0125': 'h', '\u0127': 'h', '\u0128': 'I', '\u012a': 'I', '\u012c': 'I', '\u012e': 'I', '\u0130': 'I', '\u0129': 'i', '\u012b': 'i', '\u012d': 'i', '\u012f': 'i', '\u0131': 'i', '\u0134': 'J', '\u0135': 'j', '\u0136': 'K', '\u0137': 'k', '\u0138': 'k', '\u0139': 'L', '\u013b': 'L', '\u013d': 'L', '\u013f': 'L', '\u0141': 'L', '\u013a': 'l', '\u013c': 'l', '\u013e': 'l', '\u0140': 'l', '\u0142': 'l', '\u0143': 'N', '\u0145': 'N', '\u0147': 'N', '\u014a': 'N', '\u0144': 'n', '\u0146': 'n', '\u0148': 'n', '\u014b': 'n', '\u014c': 'O', '\u014e': 'O', '\u0150': 'O', '\u014d': 'o', '\u014f': 'o', '\u0151': 'o', '\u0154': 'R', '\u0156': 'R', '\u0158': 'R', '\u0155': 'r', '\u0157': 'r', '\u0159': 'r', '\u015a': 'S', '\u015c': 'S', '\u015e': 'S', '\u0160': 'S', '\u015b': 's', '\u015d': 's', '\u015f': 's', '\u0161': 's', '\u0162': 'T', '\u0164': 'T', '\u0166': 'T', '\u0163': 't', '\u0165': 't', '\u0167': 't', '\u0168': 'U', '\u016a': 'U', '\u016c': 'U', '\u016e': 'U', '\u0170': 'U', '\u0172': 'U', '\u0169': 'u', '\u016b': 'u', '\u016d': 'u', '\u016f': 'u', '\u0171': 'u', '\u0173': 'u', '\u0174': 'W', '\u0175': 'w', '\u0176': 'Y', '\u0177': 'y', '\u0178': 'Y', '\u0179': 'Z', '\u017b': 'Z', '\u017d': 'Z', '\u017a': 'z', '\u017c': 'z', '\u017e': 'z', '\u0132': 'IJ', '\u0133': 'ij', '\u0152': 'Oe', '\u0153': 'oe', '\u0149': "'n", '\u017f': 's' }; const basePropertyOf = (object) => (key) => object[key]; const characterMap = basePropertyOf(latinUnicodeLetters); /** * Replace accented characters in Google Sheets with English letters. * * @param {string} input The input string with accented characters. * @return The input without accented characters. * @customfunction */ function REPLACE_ACCENTED(input) { if (input && typeof input === 'string') { return input.replace(latinRegEx, characterMap).replace(comboRegEx, ''); } return input; } View the full article

You have a workbook in Google Sheets that contains multiple rows of data and you are required to sort the list in a random order. For instance, your sheet may contain the names of your team members and you need to reshuffle the list before assigning tasks to each of the members randomly. Or your Google Sheet may have the email addresses of people who participated in a giveaway and you need to pick any three random entries in an unbiased manner for the prize. There are multiple ways to randomize the data rows in Google Sheet. You can either use the built-in SORT function of Google Sheets or create a menu-based function that lets you randomize data with a click. Demo - Make a copy of this Google Sheet to try random sort with your own data in sheets. Sort Google Sheets in Random Order Open your Google Sheet that contains the list of data and create a new sheet. Paste the following formula in A1 cell of this empty sheet. =SORT(Customers!A2:D50, RANDARRAY(ROWS(Customers!A2:A50), 1), FALSE) The first argument of the SORT function specifies the range of data that needs to be sorted in A1 Notation, the second argument creates a virtual column of same dimension but filled with random numbers and third order specifies the sort order from smallest to largest. You may also want to replace Customers in the formula with the exact name of your Google Sheet. If the sheet name contains spaces, enclose your sheet name in single as quotes like 'Employee List'!A2:D50. We start with row 2 since the first row is assumed to contain the header (titles). The advantage with this approach is that it doesn’t alter the source of data as the randomized list of data appears in a new sheet. Sort a List Randomly in Google Sheets with Apps Script If you prefer a more automated approach that doesn’t require you to manually add formulas each time you need to perform a random sort, take the Apps Script route. Open your Google Sheet, go to the Tools menu and choose Script editor. Copy-paste the following code in the editor and save. Reload the Google Sheet and you should see a new menu as shown in the screenshot above. /** @OnlyCurrentDoc */ // Sort data in random order const sortRowsInRandomOrder = () => { // Get the current sheet that contains the list of data const sheet = SpreadsheetApp.getActiveSheet(); // Get the first non-empty column const column = sheet.getLastColumn() + 1; // Add the RAND() formula to all rows in the new column sheet.getRange(1, column).setFormula('=RAND()').autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); // Sort the entire range of data using the random values // Do not include the first row of data (header) for sort sheet.getDataRange().offset(1, 0).sort({ column }); // Remove the temporary column from Google sheet sheet.deleteColumn(column); // Flush the changes SpreadsheetApp.flush(); }; // Add the menu to Google Sheets const onOpen = () => { SpreadsheetApp.getUi().createMenu('Randomize Rows').addItem('Start', 'sortRowsInRandomOrder').addToUi(); }; Keep Shuffling Rows Go to the Randomize Rows menu and choose Start. It creates a temporary column, fill the RAND() formula in the new column for the entire range of cells, sorts the sheet range by this data and then remove the temporary column automatically. You can click the same menu item multiple times and it will keep shuffling the rows in random order. View the full article

Apple has changed its App Store rules in the U.S. to let apps link users to their own websites so they can buy subscriptions or other digital goods. This change comes after a U.S. court ruled in favor of Epic Games in a case against the iPhone maker, ordering the latter not to prohibit apps from including features that could redirect users to their own websites for making digital purchases. “The App Review Guidelines have been updated for compliance with a United States court decision regarding buttons, external links, and other calls to action in apps,” Apple said in a blog post. The lawsuit that Epic Games brought in 2020 concerned the amount of control Apple had over transactions done in apps hosted on its App Store. In 2021, the game studio won an injunction that ordered Apple to give developers more options to redirect users to their own websites so they could avoid paying the tech giant a 30% cut. After its appeal against the injunction failed, Apple last year started allowing other apps to link out and use non-Apple payment mechanisms, but it still took a 27% commission, and also added what critics called “scare screens.” This week’s ruling means Apple must stop showing these “scare screens,” and the company has already removed guidelines around how these screens and links should contain certain language. We have asked Apple to confirm if it would stop charging apps a commission on payments made via external links, and we will update the story if we hear back. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW As for Apple, it’s not happy with the ruling. “We strongly disagree with the decision. We will comply with the court’s order and we will appeal,” the company said in a statement. Spotify, which has also been fighting with Apple over the same issue in various geographies, has already submitted a version of its app to the U.S. App Store with links to let users buy its subscription externally.

Notion, my absolute favorite tool for storing all sorts of things from web pages to code snippets to recipes, just got better. They’ve released a public API and thus it will be a lot easier for developers to read and write to their Notion workspace from external apps. For instance, you can create a document in Google Docs and export it to Notion while staying inside Docs. Google Sheets users can pull pages from Notion database into their spreadsheet. Any new submissions in Google Forms can be directly saved to Notion and so on! Save Gmail Messages in Notion I have put together a Gmail add-on that makes it easy for you to save email messages, or any other text content, from Gmail to your Notion workspace with a click. Here’s how the app works. Step 1: Connect Gmail to Notion Step 2: Allow Access to Notion pages - if you have multiple databases in your Notion workspace, you have an option to grant access to select databases and the rest will be inaccessible to the external app. Step 3: Choose Email - open any email message in Gmail and you’ll have an option to edit the content of the email subject and body before sending the content to your Notion page. Please note that the app only supports plain text format at this time. Step 4: Open Notion - As soon as you hit the Send to Notion button, the content of the currently selected email message is added to your Notion database. You can click the All updates link in your Notion sidebar to view to recently added page. How to Use Notion with Google Apps Script If you would to integrate your own Google add-on with Notion API, here’s a brief outline of the steps involved. Go to notion.so and click the Create New Integration button. You’ll be provided with a Client ID and Client Secret that you’ll need in a later step. Include the OAuth2 library in your Apps Script project and invoke the getRedirectUri method to get the OAuth2 redirect URL for the previous step. const getNotionService = () => { return OAuth2.createService('Notion') .setAuthorizationBaseUrl('https://api.notion.com/v1/oauth/authorize') .setTokenUrl('https://api.notion.com/v1/oauth/token') .setClientId(CLIENT_ID) .setClientSecret(CLIENT_SECRET) .setCallbackFunction('authCallback') .setPropertyStore(PropertiesService.getUserProperties()) .setCache(CacheService.getUserCache()) .setTokenHeaders({ Authorization: `Basic ${Utilities.base64Encode(`${CLIENT_ID}:${CLIENT_SECRET}`)}` }); }; const authCallback = (request) => { const isAuthorized = getNotionService().handleCallback(request); return HtmlService.createHtmlOutput(isAuthorized ? 'Success!' : 'Access Denied!'); }; const getRedirectUri = () => { console.log(OAuth2.getRedirectUri()); }; Connect to Notion API - Make a Get HTTP request to the /vi/databases to fetch a list of all databases that the user has explicitly shared with authorized app. function getDatabasesList() { var service = getNotionService(); if (service.hasAccess()) { const url = 'https://api.notion.com/v1/databases'; const response = UrlFetchApp.fetch(url, { headers: { Authorization: `Bearer ${service.getAccessToken()}`, 'Notion-Version': '2021-05-13' } }); const { results = [] } = JSON.parse(response.getContentText()); const databases = results .filter(({ object }) => object === 'database') .map(({ id, title: [{ plain_text: title }] }) => ({ id, title })); console.log({ databases }); } else { console.log('Please authorize access to Notion'); console.log(service.getAuthorizationUrl()); } } Download Gmail to Notion The Gmail to Notion app is in beta. If you would like to use it with your Gmail or Google Workspace account, please install from here - Gmail to Notion View the full article

Dates are internally stored as sequential serial numbers in Google Sheets. This serial number represents the number of days elapsed since December 31, 1899. You can use the DATEVALUE function to convert any date input to a number that represents the date. For instance, both the functions DATEVALUE("Jan 1") and DATEVALUE("01-Jan-2021") return the same number (44197) though the inputs have vastly different formats. The function TODAY() returns the current date while the function NOW() returns the current date and time. Both these functions do not require any arguments and they update when any cell in the Google Sheet is changed. The function NOW() + 2 returns the current date and time plus two days while NOW() - 9/24 returns the date and time 9 hours ago since 1 = 24 hours. The functions YEAR(), MONTH() and DAY() can be used extract the year, month and day of the date that is passed as an argument. The DAYS() function calculates the number of days between two dates. Internally, it calculates the DATEVALUE of the first date and the DATEVALUE of the second date and subtracts the two numbers. If you want to calculate the number of months between two dates, you can use the DATEDIF() function with the third argument set to M. For instance, the function =DATEDIF("Jan 1, 1951", TODAY(), "M") returns the number of months between January 1951 and today. The YEARFRAC() function calculates the number of years that has passed between two dates. Tip: You may use these date functions in Google Sheets with Array Formulas to schedule emails with Gmail Mail Merge. Use the EDATE() function to calculate a date that is a specified number of months before or after a specified date. For instance, EDATE(TODAY(), -1) returns the date that is one month before the current date. The EOMONTH() function helps you calculate the last day of the given month. For instance, EOMONTH(TODAY(), -1) returns the last day of the previous month. Add 1 to the result, =EOMONTH(TODAY(),-1)+1, and you’ll get the first day of the current month. The WEEKDAY() function returns the day of the week corresponding to a date with Sunday representing 1, the first day of the week. Set the second argument to 2 and days of the week will be numbered starting with Monday. The WORKDAY() function calculates the date that is a specified number of days before or after a specified date, excluding weekends. For instance, WORKDAY(TODAY(), -7) returns the date that is 7 working days before the current date. Likewise, the NETWORKDAYS() function calculates the number of working days between two dates provided as arguments. Combine this with EOMONTH to calculate the number of working days that are left till the end of the the current month =NETWORKDAYS(TODAY(), EOMONTH(TODAY(),0)) Google Sheets Date Formulas for Common Scenarios TaskWorking FormulaAdd number of days to a date=A1 + 5Get a day that is 6 months prior to a date=EDATE(A1, -5)Add number of years to a date=DATE(YEAR(A1) + 5, MONTH(A1), DAY(A1))Difference in days between two dates=DAYS(A1, A2)Total working days between two dates=NETWORKDAYS(A1, A2)Get a date that is 10 working days from now=WORKDAY(TODAY(), 10)Get the total number of months between two dates=DATEIF(A1, A2, "M")Get the difference in years between two dates=DATEIF(A1, A2, "Y")Get the number of days in the current month=EOMONTH(TODAY(), 0) - (EOMONTH(TODAY(), -1) + 1)Print the day of the week=TEXT(TODAY(), "ddddd")Calculate the age in years=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1))Days until your next birthday=DAYS(DATE(YEAR(A1)+DATEDIF(A1,TODAY(),"Y")+1, MONTH(A1),DAY(A1), TODAY())Months and days between two dates=DATEDIF(A1,A2,"YM")&" months, "&DATEDIF(A1,A2,"MD")&" days"You can copy this Google Sheet to get all the working formulas mentioned in this tutorial. View the full article

Razorpay is a popular payment gateway in India that allows you to accept online payments from customers anywhere in the world. Your customers can pay with credit cards, debit cards, Google Pay, Walmart’s PhonePe and other UPI apps. Razorpay, similar to Stripe, offers a simple no-code tool for generating payment links that you can share with customers over SMS, WhatsApp, or email. When a customer clicks on the link, they are redirected to a secure checkout page hosted on Razorpay where they can can make the payment using their preferred payment method. Here’s a sample payment link generated with Razorpay - https://rzp.io/i/6uBBFWBfv Generate Payment Links with Razorpay It takes one easy step to generate payment links with Razorpay. Sign-in to your Razorpay account, go to the Payment Links section and click on the Create Payment Link button. The built-in wizard is perfect for generating a few links but if you are however looking to generate payment links in bulk for multiple products and varying amounts, Google Sheets can help. Here’s a sample demo: Generate Payment Links with Google Sheets To get started, open your Razorpay dashboard, go to Settings > API Keys > Generate Key to generate the Key Id and Key Secret for your account. Next, make a copy of the Razorpay sheet in your Google Drive. Go to Tools > Script Editor and replace the Key Id and Key Secret with the ones generated in the previous step. Then, click on the Run menu to authorize the script with your Google Account. Switch to the Google Sheet and you can now use the custom Google Sheets function RAZORPAY() to generate dynamic payment links. If you would like to generate payment links for multiple rows in the Google Sheet, just write the formula in the first row and drag the crosshairs to the other rows as show in the demo below. Array Formulas are not supported yet. Email Payment Links to Customers You can use Mail Merge with Gmail to request payments from your customers over email. If the column title is Payment Link in Google Sheets, simply put {{Payment Link}} in the email template and these will be replaced with the actual Razorpay payment links customized for each customer. You may also use Document Studio to create PDF invoices and embed the payment links directly in the invoice. Please watch this video tutorial to learn more. How Razorpay Works with Google Sheets If you are curious to know how integration of Google Sheets and Razorpay works, the answer is Google Apps Script. The underlying code invokes the Razorpay API with your credentials and writes the generated payment links in the Google Sheet. The custom Google Sheets function uses the built-in caching service of Apps Script to reduce latency and improve performance. const RAZORPAY_KEY_ID = '<>'; const RAZORPAY_KEY_SECRET = '<>'; /** * Generate payment links for Razorpay in Google Sheets * * @param {number} amount The amount to be paid using Razorpay * @param {string} currency The 3-letter currency code (optional) * @param {string} description A short description of the payment request (optional) * @return Razorpay Payment Link * @customfunction */ const RAZORPAY = (amount, currency, description) => { const payload = JSON.stringify({ amount: amount * 100, currency, description }); // Use caching to improve performance const cachedLink = CacheService.getScriptCache().get(payload); if (cachedLink) return cachedLink; // Generate the Authorization header token const base64token = Utilities.base64Encode(`${RAZORPAY_KEY_ID}:${RAZORPAY_KEY_SECRET}`); // Invoke the Razorpay Payment Links API const response = UrlFetchApp.fetch('https://api.razorpay.com/v1/payment_links/', { method: 'POST', headers: { Authorization: `Basic ${base64token}`, 'Content-Type': 'application/json' }, muteHttpExceptions: true, payload: payload }); // The short_url contains the unique payment link const { short_url = '' } = JSON.parse(response); // Store the generated payment link in the cache for 6 hours CacheService.getScriptCache().put(payload, short_url, 21600); return short_url; }; View the full article

You run a coffee shop and you are looking for a spreadsheet formula to quickly look up prices of the product that your customer has ordered. You have the price matrix stored in a Google Sheet with the names of beverages in one column and the quantity-wise prices in the adjacent columns. When a customer selects their favorite beverage and the cup size, you can use the MATCH function to find the relative position of the column and row in the price table that matches the selected beverage and quantity. Next, use the INDEX function to find the actual price of the beverage in the selected quantity. In our Starbuck Coffee example, the coffee prices are stored in the range B2 . The customer’s beverage name (Caffè Mocha in this example) is stored in the cell G3. The following MATCH function will return the relative position of the selected beverage from the list of beverages. =MATCH(G3, $B$2:$B$11, 0) The third parameter of the MATCH function is set to 0 since we want the exact match and our price list is not sorted. Similarly, the next MATCH function will return the relative position of the column that contains the price of the beverage based on the selected quantity. The cup sizes are stored in the range C2 . The selected cup size is stored in the cell H3. =MATCH(H3, $B$2:$E$2, 0) Now that we know the relative row and column position of the price value we are looking for, we can use the INDEX function to find the actual price from the table. =INDEX($B$2:$E$11, H5, H7) Use Vlookup with ArrayFormula and Match For the next example, we have a customer order that contains multiple beverages, one per row. We want to find the price of each beverage and the total price of the order. Array Formulas will be a perfect fit here since we want to extend the same formula to all rows of the spreadsheet. However, we’ll have to revisit our approach since the INDEX function used in the previous example cannot be used with Array Formulas as it cannot return multiple values. We’ll replace INDEX with a similar VLOOKUP function and combine it with the MATCH function to perform a two-way lookup (find the beverage by name and then look for the specific cup size). The VLOOKUP function syntax, in simple English, is: =VLOOKUP( What you want to look for (beverage name), Where you want to look for it (price table range), The column number containing the matching value (chosen cup size), Return an approximate or exact match (True or False) ) The function will look for the beverage name in the specified price range (B2 ) and, from the matching row, return the value of the cell in the column that corresponds to selected cup size. The price range is not sorted so we will put FALSE for the fourth parameter. The MATCH function will return the relative position of the column that contains the price of the selected quantity of the matching beverage: =MATCH( What are you looking for (cup size), Where are you looking for it (cup size header range), 0 if you want to find the exact value (default is 1) ) If a row doesn’t contain the beverage name, the formula will return #N/A and thus we wrap the value in IFNA to prevent the formula from returning any errors. Our final formula will thus look like: =ARRAYFORMULA(IFNA(VLOOKUP(B14:B, $B$2:$E$11, MATCH(C14:C, $B$2:$E$2, 0), FALSE))) Download the Excel file - Price Lookup Sheet View the full article

Here we have an employee list spreadsheet with a column named Employee Name and a column named Employee ID. As soon as you enter a new employee name in the Employee Name column, the Employee ID column will automatically be filled with the help of an ARRAY FORMULA provided below: =ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID", IF(NOT(ISBLANK(A:A)), ROW(A:A)-1, ""))) The formula adds the current row number to the Employee ID column if the current row is not the first row. If the current row is the first row, then the title is added to the cell. Also see: Google Sheets Formulas for Google Forms The system works but there’s one major flaw in this approach. Let me explain: Say you have several new employees and you would like to add them to the spreadsheet programmatically with the help of Google Apps Script. You’ll get the reference of the sheet and then use the the getLastRow() method to find the last row number to return the first empty row that does not contain any data. function addNewEmployees() { const employees = ['Richard', 'Elizabeth', 'Orli']; const sheet = SpreadsheetApp.getActiveSheet(); const lastRow = sheet.getLastRow(); Logger.log('Last row is %s', lastRow); } The above code will return 10 and not 4 as you would have expected. The reason is that the ArrayFormula affects the getLastRow() method since it outputs an array of blank values all the way to the bottom of the sheet. Thus the output of getLastRow() and getMaxRows() would be the same if the ArrayFormula is not constrained to size of range that contains actual data. The fix is surprisingly simple. If the condition in ArrayFormula is not met, leave the second argument blank as show below. The last comma is required though else it will output the default value of FALSE. =ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID", IF(NOT(ISBLANK(A:A)), ROW(A:A)-1,))) Here’s the final working code: function addNewEmployees() { const employees = ['Richard', 'Elizabeth', 'Orli']; const sheet = SpreadsheetApp.getActiveSheet(); const lastRow = sheet.getLastRow(); Logger.log('Last row is %s', lastRow); sheet.getRange(lastRow + 1, 1, employees.length, 1).setValues(employees.map((e) => [e])); } You may use this approach to add unique IDs to your Google Sheets. If it is difficult for you to rewrite the formulas in your Google Sheet, alternate approach would be to get all the data in the sheet and look for the last row that contains data. We reverse the array to look from blank rows from the bottom and stop as soon as any row containing data is found. function getLastRow() { const data = SpreadsheetApp.getActiveSheet() .getRange('A:A') .getValues() .reverse() .map(([employee]) => employee); const { length } = data; for (var d = 0; d < length; d++) { if (data[d]) { Logger.log('The last row is %s', length - d); return length - d; } } return 1; } View the full article

This tutorial will show you how to import PayPal transactions into Google Sheets with the help of Google Apps Script. You can choose to import standard PayPal payments, recurring subscription payments, donations, or even refunds and chargebacks into Google Sheets. Once the data has been imported into Google Sheets, you can export them into a CSV file and import them into Quickbooks accounting software. Tally users in India can export PayPal transactions from Google Sheets into XML format and bulk import them into Tally. Also see: Automate PayPal with Google Forms Import PayPal Transactions in Google Sheets For this example, we will be importing the list of donors into Google Sheets who have made the donations through PayPal. 1. Create API credentials inside PayPal Sign-in to your PayPal developer dashboard (developer.paypal.com) and create a new app in the live mode. Give your App a name - Transaction Importer for Google Sheets and click the Create App button. PayPal will create a Client ID and Client Secret key that you will need in a later step. Under the Live App settings section, check the Transaction Search option and turn off all other options since we only want the API keys to list transactions and have no other functionality. Click Save to continue. 2. Create a Google Sheets Project Go to sheets.new to create a new Google Sheet. Go to Extensions menu and choose Apps Script to open the Apps Script editor. Copy-paste the code in the editor. Remember to replace the transaction code with your own. You can use T0002 for PayPal Subscriptions, T0014 for Donation payments, or T1107 for PayPal Refunds and chargebacks. The /* @OnlyCurrentDoc */ comment is a Google Apps Script comment that tells Google Apps Script to only run the code inside the current Google Sheet and not require access to any another spreadsheet in your Google Drive. /* @OnlyCurrentDoc */ /* Author: digitalinspiration.com */ const TRANSACTION_TYPE = 'T0001'; // Enter your own PayPal Client ID and Client Secret key const PAYPAL_CLIENT_ID = ''; const PAYPAL_CLIENT_SECRET = ''; // Enter start and end dates in the format YYYY-MM-DD const START_DATE = '2022-03-01'; const END_DATE = '2022-03-15'; // Generate the PayPal access token const getPayPalAccessToken_ = () => { const credentials = `${PAYPAL_CLIENT_ID}:${PAYPAL_CLIENT_SECRET}`; const headers = { Authorization: ` Basic ${Utilities.base64Encode(credentials)}`, Accept: 'application/json', 'Content-Type': 'application/json', 'Accept-Language': 'en_US' }; const options = { method: 'POST', headers, contentType: 'application/x-www-form-urlencoded', payload: { grant_type: 'client_credentials' } }; const request = UrlFetchApp.fetch('https://api.paypal.com/v1/oauth2/token', options); const { access_token } = JSON.parse(request); return access_token; }; // Append the query parameters to the PayPal API URL const buildAPIUrl_ = (queryParams) => { const baseUrl = [`https://api-m.paypal.com/v1/reporting/transactions`]; Object.entries(queryParams).forEach(([key, value], index) => { const prefix = index === 0 ? '?' : '&'; baseUrl.push(`${prefix}${key}=${value}`); }); return baseUrl.join(''); }; // Fetch the list of PayPal transaction const fetchTransactionBatchFromPayPal = (queryParams) => { const options = { headers: { Authorization: `Bearer ${getPayPalAccessToken_()}`, 'Content-Type': 'application/json' } }; const request = UrlFetchApp.fetch(buildAPIUrl_(queryParams), options); const { transaction_details, total_pages } = JSON.parse(request); return { transaction_details, total_pages }; }; // Extract the transaction details including the transaction ID, // donation amount, transaction date and buyer's email and country code const parsePayPalTransaction_ = ({ transaction_info, payer_info }) => [ transaction_info.transaction_id, new Date(transaction_info.transaction_initiation_date), transaction_info.transaction_amount?.value, transaction_info.transaction_note || transaction_info.transaction_subject || '', payer_info?.payer_name?.alternate_full_name, payer_info?.email_address, payer_info?.country_code ]; const fetchPayPalTransactions_ = () => { const startDate = new Date(START_DATE); const endDate = new Date(END_DATE); startDate.setHours(0, 0, 0, 0); endDate.setHours(23, 59, 59, 999); const transactions = []; const params = { start_date: startDate.toISOString(), end_date: endDate.toISOString(), page_size: 100, transaction_type: TRANSACTION_TYPE, fields: 'transaction_info,payer_info' }; for (let page = 1, hasMore = true; hasMore; page += 1) { const response = fetchTransactionBatchFromPayPal({ ...params, page }); const { transaction_details = [], total_pages } = response; transaction_details.map(parsePayPalTransaction_).forEach((e) => transactions.push(e)); hasMore = total_pages && total_pages > page; } return transactions; }; // Import the transactions from PayPal and write them to the active Google Sheet const importTransactionsToGoogleSheet = () => { const transactions = fetchPayPalTransactions_(); const { length } = transactions; if (length > 0) { const sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange(1, 1, length, transactions[0].length).setValues(transactions); const status = `Imported ${length} PayPal transactions into Google Sheets`; SpreadsheetApp.getActiveSpreadsheet().toast(status); } }; 3. Run PayPal Import Function Inside the script editor, click the Run button to import transactions from PayPal. You may have to authorize the script since it requires permissions to connect to the PayPal API and also write data to Google Sheets on your behalf. That’s it. If there are any PayPal transactions to import in the selected date range, the script will run and the transactions will be imported into Google Sheets. In the next part of the tutorial, we will learn how to export the PayPal transactions from Google Sheets to an XML file for importing into Tally accounting software. Also see: Send PayPal Invoices from Google Sheets View the full article

The Mail Merge for Gmail add-on lets you send personalized emails via Gmail but wouldn’t it be nice if a similar solution existed for sending personalized SMS to your contacts directly from your mobile phone? There are services, Twilio SMS for example, that let you send text messages programmatically to any phone number in the world. You can either build an SMS solution on top of these messaging APIs or you can take a simpler and less expensive route - build your own text-sending app with Google Sheets and MIT’s App Inventor. Play ; Before getting the implementation, let me give you a quick demo of our text-messaging app for sending SMS from any Android phone. You can send texts to any number in your country as well as global phone numbers if international texting is enabled on your phone. You’ll pay the standard text messaging rates as per your cellular plan. Here’s my Google Sheet with the source data. The Google Sheet can have multiple columns for SMS personalisation but the three essential columns that should be present in the sheet are Phone (for your contact’s phone number), Status (whether the SMS was sent to that phone) and Text (the personalized text message). You can use ArrayForumula with simple concatenation to build the text messages string from different columns as shown below: =ArrayFormula( IF(NOT(ISBLANK(A2:A)), A2:A & " " & B2:B & " - I will see you in " & C2:C, ) ) Now that your source data is ready in the sheets, we will use Google Apps Script to convert our Google sheets data into an API. This would enable our Android app to read the sheets’ data with a simple HTTPS request. Inside the sheets, go to Tools, Script Editor and paste this code. const SHEET_URL = "YOUR_GOOGLE_SHEET_URL"; const SHEET_NAME = "SMS"; const doGet = () => { const sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME); const [header, ...data] = sheet.getDataRange().getDisplayValues(); const PHONE = header.indexOf("Phone"); const TEXT = header.indexOf("Text"); const STATUS = header.indexOf("Status"); const output = []; data.forEach((row, index) => { if (row[STATUS] === "") { output.push([index + 1, row[PHONE], row[TEXT]]); } }); const json = JSON.stringify(output); return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.TEXT); }; const doPost = e => { const sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME); const [header] = sheet.getRange("A1:1").getValues(); const STATUS = header.indexOf("Status"); var rowId = Number(e.parameter.row); sheet.getRange(rowId + 1, STATUS + 1).setValue("SMS Sent"); return ContentService.createTextOutput("").setMimeType(ContentService.MimeType.TEXT); }; Next, go to the Publish menu in the Google Script Editor, choose Deploy as web app. Choose “Me” under “Execute the App” setting and “Anyone, even anonymous” under the “Who has access” setting. Click the Deploy button and you’ll be presented with a secret API URL that we’ll require in the next step. Do NOT share this API URL with anyone. Now that our sheets API is ready, we’ll build an Android app that will read the list of text messages and phone numbers from our Google Sheets and send the SMS messages. The texts go directly from your phone SIM instead of using any third-party SMS gateway service. Build SMS App for Android without Coding You’d normally need to know programming languages like Flutter or Java to build Android apps but in this tutorial, we’ll use MIT’s App Inventor, a simple way to develop fully functional apps with drag-n-drop. Sign-in to the appinventor.mit.edu website with your Google account and create a new App. While you are in the design mode, drag the following components on to your app: User Interface, ListView -> For displaying the message list fetched from Google Sheets. User Interface, Button -> For fetching messages from Google Sheets and for sending SMS messages from the Android app. Connectivity, Web -> For making GET and POST requests to Apps Script. User Interface, Notifier -> For displaying progress bars and alerts Social, Texting -> For sending the SMS messages. Next switch to the Blocks section inside App Inventor and design the blocks as explained in the video tutorial. We are almost done. Go to the Build menu inside App Inventor, choose App (provide QR code for .apk) and scan the QR code with your phone. It will download an APK file on the phone, install the APK and you are ready to send text messages. View the full article

This tutorial explores the different options for inserting images in Google Sheets. We’ll also discuss the advantages and limitations of each method. Use the IMAGE function to insert images into any cell of your Google Sheets. Use the Insert menu in Google Sheets to directly insert images into cells. Use the CellImageBuilder API to programmatically insert images with Google Apps Script. Use the IMAGE function To add an image to a cell, highlight the cell and click F2 to enter the formula mode. Next, enter the formula =IMAGE("URL") where URL is the public web address of that image. For instance, the following formula will insert a free image in your Google Sheet. =IMAGE("https://i.imgur.com/gtfe7oc.png") Google Sheets, by default, will scale the image to fit inside the area of the selected cell but you can easily change the default settings by adding another parameter to the IMAGE function. With mode (second parameter) set to 2, the modified formula =IMAGE("URL", 2) will stretch the image to fit inside the selected cell occupying the entire height and width of the cell. This may distort the image if the aspect ratio of the image does not match the aspect ratio of the cell. Set the mode value to 3, as in =IMAGE("URL", 3), and the image will be embedded into the cell using the original dimensions of the image. Uf the cell is too small to fit the image, the image will be cropped. Finally, You can also specify the height and width of the image in pixels by setting the mode to 4. For instance, the formula =IMAGE("URL", 4, 100, 100) will embed the image at 100x100 pixels. DescriptionImage Formula ExampleResize image to fit the cell=IMAGE(“URL”, 1)Stretch image to fit the cell=IMAGE(“URL”, 2)Use image’s original size=IMAGE(“URL”, 3)Specify custom size of the image=IMAGE(“URL”, 4, heightInPixels, widthInPixels) If you are getting parsing errors, you are either using a non-existent image or you may have missed adding quotes around the image URL inside the Image function formula. Use the Insert menu in Google Sheets You can insert images from your computer into Google Sheets by using the Insert > Image menu in Google Sheets. Choose the Insert image over cells option and select the image you want to insert. Unlike the IMAGE function that restricts you to a specific cell, this approach lets you place the image anywhere inside the Google Sheet. The image placed in this manner can be resized easily by dragging the blue handles and you can provide an Alt text to the image for better accessibility. The other advantage of this approach is that you can assign a Google Script to the image that will be executed when someone clicks the image. For instance, you may add a button in the Google Sheet and assign a script that instantly downloads the sheet as a PDF file to your computer. Add Images through Apps Script Developers can also add images in Google Sheets programmatically either using the setFormula() method or the CellImageBuilder API of Google Apps Script. Using the setFormula() method This script will insert a public image from the web into the first cell (A1) of the active Google Sheet. Since we have not specified the mode in the Image formula, the image will be resized to fit the cell while maintaining the aspect ratio of the image. const insertImageWithFormula = () => { const imageUrl = 'https://i.imgur.com/gtfe7oc.png'; const sheet = SpreadsheetApp.getActiveSheet(); const cell = sheet.getRange('A1'); cell.setFormula(`=IMAGE("${imageUrl}")`); SpreadsheetApp.flush(); }; Using the CellImageBuilder API This is a relatively new feature of Google Apps Script that allows you to add images to a cell. You can specify the image URL, the alt text and the image will be resized automatically to fit in the specified cell. It is recommended to use the try-catch block else the function may fail if the image URL is invalid or not accessible. const useCellImageBuilder = () => { try { const imageUrl = 'https://i.imgur.com/gtfe7oc.png'; const imageDescription = 'Image of a person wearing spectacles'; const cellImage = SpreadsheetApp.newCellImage() .setSourceUrl(imageUrl) .setAltTextTitle(imageDescription) .build() .toBuilder(); const sheet = SpreadsheetApp.getActiveSheet(); const cell = sheet.getRange('A11'); cell.setValue(cellImage); } catch (f) { Browser.msgBox(f.message); } }; The CellImage API also lets you use base64 encoded image strings instead of the image URL. data:image/png;charset=utf-8;base64, You can use Google Apps script to convert an image to base64 encoded string and pass the base64 string string to the CellImageBuilder API. const useCellImageBuilderWithDataURI = () => { const dataImageUri = 'data:image/png;base64,iVBORw0KGgoAAAAeCAYAA7...'; const imageDescription = 'Image credit: wikimedia.org'; const cellImage = SpreadsheetApp.newCellImage() .setSourceUrl(dataImageUri) .setAltTextTitle(imageDescription) .build() .toBuilder(); SpreadsheetApp.getActiveSheet().getRange('A11').setValue(cellImage); }; The script would require access to either the googleapis.com/auth/spreadsheets.currentonly (access current spreadsheet only) or googleapis.com/auth/spreadsheets (access all Google Spreadsheet in your Google Drive) scope to use any of the Spreadsheet functions. View the full article

Conditional formatting in Google Sheets makes it easy for you to highlight specific cells that meet a specific criteria. For instance, you can change the background color of a cell to yellow if the cell value is less than a certain number. Or you can choose to highlight an entire row or column if certain conditions are met. Highlight Individual Cells For this example, we have a sales chart that lists the names of salespeople, their state and the total sales target. We would like to highlight individual cells in the State column if the salesperson is from California. Go to the Format menu, choose Conditional Formatting, and click Add Condition. Here, choose the range as B2:B and the format condition as Text is Exactly. Then, enter the text CA in the text box, choose a custom background color and click Done. Highlight Entire Row For the same Sales chart, we would now like to highlight entire rows where the sales target is more than $8,000. Inside the formatting rule, set the range as A2:C since we would like to apply formatting to the entire table. Next, choose Custom Formula is for the formatting rules condition and set the criteria as =$C2>8000. If you would like to highlight rows where the sales target is within a range, say between $5000 and $7000, you can add the =ISBETWEEN($C2, 5000,7000) formula in the criteria box. The $ in $C2 applies the formula to the entire column C while the missing $ in front of the number 2 allows it to increment. If you want to highlight rows where the sales target is more than the average sales target, you can either use =IF(AVERAGE($C2:C)<$C2,1) or =$C2>average($C2:C) formula in the criteria box. If you wish to highlight a row that contains the maximum value for sales, you can use the =MAX() formula in the criteria box. =$C:$C=max($C:$C) Also see: Highlight Duplicate Rows in Google Sheets Formatting based on two cells In the same Sales table, we would like to highlight salespersons who are responsible for a specific state (say, “CA”) and who have a sales target of more than $5,000. We can achieve this by applying multiple conditions using the AND function as shown below: =AND(C2>5000, B2="CA") Conditional Formatting base on Date Our table has a list of invoice and the date when the invoice is due. We’ll use conditional formatting to highlight invoices that are past due for more than 30 days and send them email reminders. =DAYS(TODAY(),$B:$B)>=30 In another example, we have a list of students and their date of birth. We can use Date functions like to highlight students who are older than 16 years old and whose date of birth is in the current month. =AND(YEAR(TODAY())-YEAR($B2)>=16,MONTH($B2)=MONTH(TODAY())) Heatmaps - Format Cells by Color Scale Our next workbook contains a list of US cities and their average temperatures for various months. We can use Color Scales to easily understand the temperature trends across cities. The higher values of the temperature are more red in color and the lower values are more green in color. Mark Rows Containing one of the values With conditional formatting in Google Sheets, you can easily highlight rows that contain a specific value. For example, you can highlight all rows that contain the value CA in the State column. However, if you want to highlight rows that contain one of multiple values, you can either use the OR function or, better still, use Regular Expressions with the custom formula. This formula will highlight all rows that contain either CA or NY or FL in the State column. =REGEXMATCH(UPPER($B:$B), "^(CA|NY|FL)$") Alternatively, you may have a list of states listed in another sheet and use MATCH with INDIRECT to highlight rows that contain one of the states. =MATCH($B1, INDIRECT("'List of States'!A1:A"),0) Apply Conditional Formatting to Entire Column Until now, we have explored examples of highlighting individual cells or entire rows when certain conditions are satisfied. However, you can use conditional formatting to highlight entire columns of a Google Sheet. In this example, we have sales for different years per geographic region. When the user enters the year in cell A9, the corresponding column is highlighted in the sales table. The custom formula will be =B$1=$A$9. Notice that the $ is used with the number in the cell reference since the check is made only in the first row. Conditional Formatting with Google Apps Script If you were to apply the same conditional rules to multiple Google Spreadsheets in one go, it is recommended that you automate Google Apps Script else it will take more time to apply the formatting manually. const applyConditionalFormatting = () => { const sheet = SpreadsheetApp.getActiveSheet(); const color = SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build(); const rule1 = SpreadsheetApp.newConditionalFormatRule() .setRanges([sheet.getRange('B:B')]) .whenTextEqualTo('CA') .setUnderline(true) .setBold(true) .setBackground(color) .build(); const rule2 = SpreadsheetApp.newConditionalFormatRule() .setRanges([sheet.getRange('A1:C15')]) .whenFormulaSatisfied('=$C1>5000') .setBackground('green') .setFontColor('#00FF00') .build(); const conditionalFormatRules = sheet.getConditionalFormatRules(); conditionalFormatRules.push(rule1); conditionalFormatRules.push(rule2); sheet.setConditionalFormatRules(conditionalFormatRules); }; Please check the documentation of ConditionalFormatRuleBuilder for more details. This will also help you copy conditional formatting rules from one spreadsheet to another. View the full article

The BHIM UPI payment system has transformed the way we pay for goods and services in India. You scan a QR Code with your mobile phone, enter the secret PIN and the money gets instantly transferred from your bank account to the merchant’s bank account. There’s no transaction fee, the money is transferred in real-time and no data of the payer is shared with the payee. Our online store initially accepted payments through credit cards only but after we added the UPI QR Code on the checkout page, more that 50% of customers in India are making payments through UPI. Other than instant payouts, the big advantage of UPI is that the merchant need not pay any transaction fee to PayPal or Stripe. Create Dynamic UPI QR Codes When you sign-up for any UPI app, be it PhonePe, Paytm, Google Pay, WhatsApp, Amazon Pay or any other BHIM UPI app, they will all provide you with a downloadable QR Code that you can attach in emails, invoices, embed on your website or print and paste near your billing counter. Customers will scan this QR Code, enter the billing amount, and confirm the payment. The QR code provided by UPI apps are static and thus do not include the amount that has to be paid by the customer. Our UPI QR Code generator is designed solve this problem. It generates a dynamic QR Code that includes the amount and thus the merchant can control how much the customer has to pay after scanning the QR code. Visit labnol.org/upi to generate dynamic QR codes for UPI payments. The website does not collect, store or process any of the data you enter in the QR Code form. UPI QR Code in Google Sheets If you are using Document Studio to generate customer invoices inside Google Sheets, you can write a simple function to embed the payment QR code in your PDF invoices. QR Codes can be added in emails as well that are sent through Gmail Mail Merge Go to your Google Sheet, click the Extensions menu and choose Apps Script Editior from the dropdown. Copy-paste the UPI function inside the script editor and save your project. /** * Create a UPI QR Code for payments * * @param {29.99} amount The amount requested in INR * @param {"xyz@upi"} merchant_upi UPI address of the merchant * @param {"Blue Widgets"} merchant_name Full name of the payee * @param {"250"} size The size of the QR image in pixels * @return The QR Code * @customfunction */ function UPI(amount, merchant_upi, merchant_name, size) { if (amount.map) { return amount.map(function (amount2) { return UPI(amount2, merchant_upi, merchant_name, size); }); } const googleChart = `https://chart.googleapis.com/chart?cht=qr&choe=UTF-8`; const upiData = `upi://pay?pn=${merchant_name}&pa=${merchant_upi}&am=${amount}`; return `${googleChart}&chs=${size}x${size}&chl=${encodeURIComponent(upiData)}`; } Now you can add the QR code to any cell in the Google Sheet by using the UPI function in combination with the IMAGE function as shown in the following example: =IMAGE(UPI("19.95", "digitalinspirationindia@icici", "Digital Inspiration", "200")) How UPI QR Codes are Generated Internally, the QR Code for UPI payments contains the merchant’s UPI ID, the amount to be paid and the payee name in the following format: upi://pay?pa=&pn=<payee_name>&am=<amount>&tn=<transaction_notes> If the am parameter is not provided in the UPI url, the customer will have to manually enter the amount in the UPI app before confirming the payment. The UPI deeplink specs also recommend using the mam (minimum amount) parameter to specify the minimum amount that the customer has to pay. Set its value to “null” so that the customer cannot pay less than the specified amount. You may also include custom notes in the QR code and these will be sent to you in the transaction history of your bank statement. View the full article

This tutorial describes how you can use Google Sheets to build your own podcast manager. You can specify a list of your favorite podcast shows in Google Sheets and it will automatically download new episodes to your Google Drive in neatly organized folders. The setup is very simple, the app is completely open-source and you need no programming language. How the Drive Podcast Manager Works? You have to place the links of your favorite podcasts in column A of the Google Sheet as shown in the screenshot below. The app will automatically download the latest episodes of each podcast to your Google Drive. You can open the MP3 files from your Google Drive or find them directly inside the same Google Sheet. The app will create a new folder, titled Podcasts in your Google Drive. Inside this folder, it will create sub-folders for each podcast show with the folder name same as the title of the podcast. Download Podcasts to Google Drive Here’s how you can build your own podcast manager with Google Sheets and Google Drive. Click here to make a copy of the Google Sheet in your Google account. Open the copied spreadsheet, switch to the Subscriptions sheet and enter the RSS feed links of your favorite podcasts in column A. You may use our Apple Podcasts Lookup utility to find the RSS feed of any podcast that is listed on Apple Podcasts. Go to the Extensions menu and choose Script Editor to open the underlying Google Apps Script file. Choose the Install function from the list of functions and click Run to install the app. You may have to authorize the app once since it needs permission to save files to Google Drive on your behalf. That’s it. The app will create a cron job that runs every few hours in the background and download the latest episodes of your favorite podcasts to your Google Drive. We even have a built-in MP3 player embedded inside Google Sheets that will play the latest episode of each podcast when you click the Play button. The Technical Details If you are curious to know how the whole thing works, here’re the technical details. The app uses the Spreadsheet API to read the list of podcasts from the Google Sheets. It then uses the XML service of Apps Script to parse the RSS feed and extract new podcast episodes that have been published since the last check. All podcast RSS feeds are required to have an tag with a tag inside. The tag contains the URL of the MP3 file and this is what the app uses to get the download URL of the corresponding episode. const parseRSS = (xmlUrl, lastUpdatedTime) => { const feed = UrlFetchApp.fetch(xmlUrl).getContentText(); const doc = XmlService.parse(feed); const root = doc.getRootElement(); const channel = root.getChild('channel'); const episodes = channel .getChildren('item') .map((item) => ({ date: new Date(item.getChildText('pubDate')), title: item.getChildText('title'), enclosure: item.getChild('enclosure')?.getAttribute('url')?.getValue() })) .filter(({ date }) => date > lastUpdatedTime) .filter(({ enclosure }) => enclosure); return { title: channel.getChildText('title'), episodes }; }; Once the app has a list of new episodes, it uses the UrlFetch service to download the podcasts and saves them to Google Drive in a folder specific to the podcast show. The app then writes a new row to the Google Sheet with the link of the Google Drive file and a timestamp of when the episode was downloaded. const getPodcastFolder = (folderName) => { const parentFolder = DriveApp.getFoldersByName('Podcasts').next(); const folders = parentFolder.getFoldersByName(folderName); if (folders.hasNext()) return folders.next(); return parentFolder.createFolder(folderName); }; const downloadPodcast = (podcastTitle, episodeUrl, episodeTitle) => { try { const blob = UrlFetchApp.fetch(episodeUrl).getBlob(); const folder = getPodcastFolder(podcastTitle); const file = folder.createFile(blob); SpreadsheetApp.getActiveSheet().appendRow([ new Date(), `=HYPERLINK("${episodeUrl}";"${episodeTitle}")`, `https://drive.google.com/file/d/${file.getId()}/view` ]); } catch (f) { console.error(f); } }; View the full article

You can put the link of any MP3 audio file in Google Sheets but when you click the file link, the audio would not play. You can however add a button in your Google Sheet that, when clicked, will play the MP3 file in a modal window. Here’s a demo: The audio files are hosted on Google Drive and when the Play button is clicked, the app will open a modal window with the audio player. Add the Audio Player Button To get started, create a new Google Sheet, go to the Insert menu and select the Create a New Drawing option. Select Beveled Rectangle from the list of shapes, add some inline text and click Save to insert the button to your active Google Sheet. Add the Player Script Next, inside the Extension menu of Google Sheets, go to Script Editor and paste the following script. const openAudioPlayer = () => { const cell = SpreadsheetApp.getActiveSheet().getActiveCell().getValue(); const html = ``; const dialog = HtmlService.createHtmlOutput(html).setTitle('Play').setWidth(500).setHeight(200); SpreadsheetApp.getUi().showModelessDialog(dialog, 'Play Audio'); }; Switch to the Google Sheet you created, right-click the Play button and assign the openAudioPlayer script to the button. Click OK to save your changes. Now play the URL of any MP3 file in any Google Sheet cell, click the Play button and the audio will play in a modal window. Please ensure that the cell containing the audio file link is active when you click the Play button. Also, if you are hosting the sound files in your Google Drive, the format of the link should be https://drive.google.com/file/d//preview. View the full article

In a previous tutorial, you learned how to send WhatsApp messages from Google Sheets using the official WhatsApp API. The first 1,000 messages per month for each WhatsApp Business Account are free and then you pay per use based on the country of the message sender and the message recipient. WhatsApp API Pricing For instance, if you are sending a WhatsApp message from the US phone number to a WhatsApp user in France, the cost would be 14¢ per message. However, if you send messages from WhatsApp number in India to another number in India, the cost would be around 0.006¢ per message. The rate cards for WhatsApp API pricing are available here. In addition to the cost factor, the WhatsApp Business API requires you to have a verified business on Facebook (see verification requirements) and the terms require that you will only send message to WhatsApp users who have opted-in to receive future messages from you on WhatsApp. The other limitation of WhatsApp API is that you can only send messages that are based on templates pre-approved by WhatsApp. You can however send free-form messages within 24 hours of the last user message in a conversation. WhatsApp Function for Google Sheets If you are a small business that is looking for an alternate route to message customers on WhatsApp without paying for the API, here’s a semi-automated technique. You can use the Click to Chat feature of WhatsApp to quickly send a personalized message to any phone number that’s registered on WhatsApp. For this example, we have a sheet that list the customer’s name, amount that they have to pay and the due date for payment. We can use the CONCATENATE or TEXTJOIN function of Google Sheets to create a personalized message for each client in column D. The column E of the Google Sheet contains the phone numbers of each WhatsApp user. We can use the WHATSAPP custom function to create a personalized chat link for different customers in the Google Sheet. When you click this chat link, it automatically open a WhatsApp conversation with the user and the message is pre-filled in the chat box. This method does require a few extra click but there’s no cost involved and it works for both WhatsApp Business and WhatsApp personal accounts. WhatsApp Function Here’s the underlying WHATSAPP function that generates the Click to Chat link in Google Sheets. It also supports Array Formulas. The third parameter determines whether the link should launch the WhatsApp website or the WhatsApp desktop client. You can play with the live sheet here. /** * Create WhatsApp Click to Chat Link * * @param {string} phone The phone number with country code * @param {string} message The text message * @param {boolean} web Open the message in WhatsApp web? * @return The pre-filled message link for WhatsApp. * @customfunction */ function WHATSAPP(phone, message, web) { if (Array.isArray(phone)) { return phone.map((row, index) => WHATSAPP(row[0], message[index][0]), web); } const phoneNumber = String(phone).replace(/[^\d]/g, ''); const messageText = encodeURIComponent(message); return web === true ? `https://web.whatsapp.com/send?phone=${phoneNumber}&text=${messageText}` : `https://wa.me/${phoneNumber}?text=${messageText}`; } View the full article

This Google Script converts the currently active Google Spreadsheet into a square grid of randomly colored boxes using hexadecimal numbers. Credit 五味田和則 The random colors are generated using a JavaScript method - Math.ceil(Math.random()* 0xFFFFFF).toString(16)`. Also, since the value of the cell is the same as the color code, you can copy a cell to copy its color code. function colorCodes() { var sheet = SpreadsheetApp.getActiveSheet(); for (var i = 1; i <= 100; i++) { sheet.setRowHeight(i, 20); sheet.setColumnWidth(i, 20); for (var j = 1; j <= 100; j++) { var colorcode = Math.ceil(Math.random() * 0xffffff).toString(16); sheet .getRange(i, j) .setBackground("#" + colorcode) .setValue(color); } } } View the full article

An order form, created in Google Forms, requires customers to provide their full name, the item quantity and whether home delivery is required. The final bill amount is calculated with a simple formula in Google Sheets. // Item cost is $99 per unit. Delivery cost is $19. =IF(ISNUMBER(C2), SUM(C2*99, IF(D2="Yes", 19, 0)), ) The Google Sheet owner has entered the formula across all rows in the Total Amount column so that the value is automatically calculated when a new form response is submitted. The problem is that the formulas in Google Sheets are automatically deleted when new responses come in. That’s the default behavior and even if you protect the column range, the formulas in the cell will be erased on new rows. How to Prevent Formulas from Deleting There are several workarounds to this problem. Use an ARRAYFORMULA Instead of adding formulas inside individual cells of the column, add an Array Formula to the first row of the column that contains the computed values. =ARRAYFORMULA(IF(ROW(C:C)=1, "Total Amount", IF(ISNUMBER(C:C), C:C*99 + IF(D:D="Yes",19,0),) )) Here’s a simple breakdown of the formula: IF(ROW(C:C)=1, "Total Amount", ... - If the current row number is 1, add the column title. IF(ISNUMBER(C:C), ... - Calculate the amount only if there’s a numeric value in the C column. C:C*99 + IF(D:D="Yes",19,0),) - Multiply $99 with the item quantity and add $19 if the column D is set to Yes. Use MAP with a LAMBDA function You can use the new MAP function of Google Sheets that takes an array of values as input and returns a new array formed by applying a Lambda function to each value of the array. =MAP(C:C,D:D, LAMBDA(Qty, Delivery, IF(ROW(Qty)=1,"Total Amount", IF(ISNUMBER(Qty), Qty*99 + IF(Delivery="Yes", 19,),)) )) Use a QUERY function If array formulas sound complex, here’s an alternate approach. Create a new sheet in your Google Spreadsheet and use the QUERY function with a SQL-like statement to import the required data from the Form sheet into the current sheet. =QUERY('Form Responses 1'!A:D,"SELECT A,B,C,D",TRUE) We are only importing the sheet data that has been entered in the form response and all the calculations will happen in this sheet, not the main sheet. Paste the simple formula for amount calculation in cell E2 and drag the cross-hair down to auto-fill the formula across all rows. =IF(ISNUMBER(C2), SUM(C2*99,IF(D2="Yes",19,0)),) This is the recommended approach if you would like to preserve row formatting and conditional formatting when new survey responses come in. View the full article