Ceacer

The Email Spreadsheet add-on helps you automatically email one or more sheets inside a Google Spreadsheet to multiple people. You can configure the add-on to convert and email individual sheets or the entire spreadsheet. Play ; One of the popular features of the add-on is the ability to email the selected range of cells. For instance, if you have a big sheet but you only wish to email a specific range of cells, you can use the Range option to email the selected cells. Every cell in a Google Spreadsheet has a Row, Column (R, C) coordinate. For instance, the cell A1 has Row = 1 and column = 1. The cell E8 has Row = 8 and Column = 5. When configuring the Email Spreadsheet Add-on to convert a range of cells, you need to provide the upper left cell in the range and the lower right cell in RC format. For instance, if you would like export all cells in the A1 range, the R1C1 and R2C2 values would be 1,1,8,5 respectively. In the above example, the selected range is A5 and therefore the export range is 5(R1), 1(C1), 12(R2), 3(C2) You can further select advanced options to show / hide gridlines, page numbers, etc. Internally, the add-on is powered by Google Apps Script. For more answers, please check out the Email Google Sheets docs. View the full article

The Google Apps Script uses the Advanced Drive API to covert Microsoft Excel files (XLS, XLSX) into CSV files and saves them into a specific Google Drive folder. The Excel sheets are deleted after the CSV files are saved in Drive. Also see: Convert Google Sheets to PDF Files The conversion engine may timeout if you have too many XLS/XLSX files in a Google Drive and in that case, you’d need to include the time check to ensure that the script doesn’t exceed the execution time limit. function convertXLSFilesToCSV() { var oauthToken = ScriptApp.getOAuthToken(), sourceFolder = DriveApp.getFolderById(SOURCE_XLS_FOLDER), targetFolder = DriveApp.getFolderById(TARGET_CSV_FOLDER), mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY]; /* Written by Amit Agarwal */ /* email: [email protected] */ /* website: www.ctrlq.org */ for (var m = 0; m < mimes.length; m++) { files = sourceFolder.getFilesByType(mimes[m]); while (files.hasNext()) { var sourceFile = files.next(); // Re-upload the XLS file after convert in Google Sheet format var googleSheet = JSON.parse( UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true', { method: 'POST', contentType: 'application/vnd.ms-excel', payload: sourceFile.getBlob().getBytes(), headers: { Authorization: 'Bearer ' + oauthToken, }, }).getContentText() ); // The exportLinks object has a link to the converted CSV file var targetFile = UrlFetchApp.fetch(googleSheet.exportLinks['text/csv'], { method: 'GET', headers: { Authorization: 'Bearer ' + oauthToken, }, }); // Save the CSV file in the destination folder targetFolder.createFile(targetFile.getBlob()).setName(sourceFile.getName() + '.csv'); // Delete the processed file sourceFile.setTrashed(true); } } } View the full article

You can easily import CSV files into Google Spreadsheet using the Utilities.parseCsv() method of Google Apps Script. The snippets here show how to parse and import data from CSV files that are on the web, saved on Google Drive or available as a Gmail attachments. Import CSV from an email attachment in Gmail function importCSVFromGmail() { var threads = GmailApp.search('from:[email protected]'); var message = threads[0].getMessages()[0]; var attachment = message.getAttachments()[0]; // Is the attachment a CSV file if (attachment.getContentType() === 'text/csv') { var sheet = SpreadsheetApp.getActiveSheet(); var csvData = Utilities.parseCsv(attachment.getDataAsString(), ','); // Remember to clear the content of the sheet before importing new data sheet.clearContents().clearFormats(); sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); } } Import CSV file that exists in Google Drive** function importCSVFromGoogleDrive() { var file = DriveApp.getFilesByName('data.csv').next(); var csvData = Utilities.parseCsv(file.getBlob().getDataAsString()); var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); } Fetch and import CSV file from an external website function importCSVFromWeb() { // Provide the full URL of the CSV file. var csvUrl = 'https://ctrlq.org/data.csv'; var csvContent = UrlFetchApp.fetch(csvUrl).getContentText(); var csvData = Utilities.parseCsv(csvContent); var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); } This method can however not be used for importing CSV files hosted on FTP servers as the UrlFetchApp service of Google Scripts doesn’t allow connecting to FTP servers, yet. View the full article

The spreadSheetFill function will fill the cells of the currently active Google Spreadsheet with random data using Google Scripts. The random values are computed using the Math.random() method. We could have used the rand() method of Google Spreadsheet directly but the disadvantage is the data will change / refresh every time you open the sheet or edit any cell. Open the Google Apps Script editor inside the Google Spreadsheet and copy-paste the code. Next choose SpreadSheetFill from the Run menu and authorize. /* Written by https://gist.github.com/thomaswilburn */ var rowConfig = 'timestamp name favorite note season'.split(' '); var rowMapper = function (data) { var row = []; for (var key in data) { var index = rowConfig.indexOf(key); if (index > -1) { var value; if (key in data) { value = data[key]; } else { value = ''; } row[index] = data[key]; } } for (var i = 0; i < row.length; i++) { if (typeof row[i] == 'undefined') { row[i] = ''; } } return row; }; function spreadSheetFill() { var sheet = SpreadsheetApp.getActiveSheet(); var count = 1000; var firstNames = ['Alice', 'Bob', 'Charles', 'Dawn', 'Erin', 'Fred', 'Gwen', 'Harry']; var lastNames = ['I.', 'J.', 'K.', 'L.', 'M.', 'N.']; var getRandom = function (arr) { return arr[Math.floor(Math.random() * arr.length)]; }; for (var i = 0; i < count; i++) { var position = Math.PI + Math.PI / 4 - Math.random() * Math.PI * 0.75; var distance = 5 * Math.random() + 7; var params = { timestamp: Date.now(), name: getRandom(firstNames) + ' ' + getRandom(lastNames), season: Math.random() > 0.5 ? true : '', favorite: Math.round(Math.random() * 90), note: Utilities.base64Encode( Utilities.computeDigest( Utilities.DigestAlgorithm.MD5, Math.round(Math.random() * 100000000) + '', Utilities.Charset.US_ASCII ) ), }; var row = rowMapper(params); sheet.appendRow(row); } } View the full article

Introducing Document Studio, a powerful Google add-on that lets you effortlessly generate documents and reports using merge data stored inside Google Sheets. It can also create documents with live data from Google Forms submissions. The generated documents can be automatically sent inside personalized email messages using built-in Mail Merge. There’s zero learning curve and you can work inside the familiar Google Docs environment. The documents can be produced in PDF, Word, Excel, PowerPoint and several other formats. The generated files are uploaded to your Google Drive, they can be instantly shared and there’s also an option to send files directly to the printer via Google Cloud Print. The uses cases are endless. Businesses can streamline workflows and send professional looking invoices to customers. A school teacher can print individual exam certificates of every student in her classs. An instructor can generate personalized training handouts for attendees. Play ; Getting Started with Document Studio It takes 3 easy steps to get started. Create a template inside Google Docs, Google Sheets or Google Slides and add placeholders, such as {{Name}} or {{Address}}, for merge fields. Create a Google Sheet with the source data. The column headings will correspond to the placeholders in the template while the rows represent the records. Install the document merge add-on and follow the wizard to produce the documents. Document Studio will create one document per row in the sheet. Document Studio includes a WYSIWYG email editor to help you send the merged documents to one or more recipients inside personalized email messages. If your template is a Google Spreadsheet and it contains any formulas, they are re-calculated and evaluated in the generated document. You can also include unique images, Google Maps, QR Code images and PayPal payment links in generated documents with the help of simple spreadsheet functions. In addition to document merge, the add-on can also be used for creating documents in real-time from Google Forms submissions. Watch the video tutorial on YouTube or check the help center to learn more about Document Studio. View the full article

The school has a Google Form where teacher enter the performance grades of their students. This form data is stored in a Google Spreadsheet and they are using Google Charts with the Google Visualization API to convert these tables of rows into visual charts that can be easily visualized. The principal needs a public dashboard (in other words, a web page) where the charts can be displayed to external users without having to give them access to the Google Spreadsheet. This can be easily done with the HTML Service of Google Script and the Google Visualization API. Here’s a simple example that fetches data from a Google Spreadsheet and display the corresponding chart on a web page using the HTML Service. The Google script needs to be published as a Web App and the access should be set to anyone (including anonymous) or you can also limit it to users of your own Google Apps Domain. // Code.gs function doGet(e) { return HtmlService.createTemplateFromFile('index') .evaluate() .setTitle('Google Spreadsheet Chart') .setSandboxMode(HtmlService.SandboxMode.IFRAME); } function getSpreadsheetData() { var ssID = 'PUT_YOUR_SPREADSHEET_ID', sheet = SpreadsheetApp.openById(ssID).getSheets()[0], data = sheet.getDataRange().getValues(); return data; } Next create an html file inside the script editor and save it as index.html DOCTYPE html> <html> <head> <script src="https://www.google.com/jsapi">script> head> <body> <div id="main">div> <script> google.load('visualization', '1', { packages: ['corechart', 'bar'] }); google.setOnLoadCallback(getSpreadsheetData); function getSpreadsheetData() { google.script.run.withSuccessHandler(drawChart).getSpreadsheetData(); } function drawChart(rows) { var options = { title: 'Population (in Millions)', legend: 'none', chartArea: { width: '60%' }, vAxis: { textStyle: { fontFamily: 'Arial', fontSize: 12 } } }; var data = google.visualization.arrayToDataTable(rows, false), chart = new google.visualization.BarChart(document.getElementById('main')); chart.draw(data, options); } script> body> html> The above example fetches the rows of a single sheet but if your dashboard is complex and requires you to fetch data from multiple sheets in a spreadsheet, you can use the JSON form. On the server side, create a JSON of your data and pass that to the HTML template after converting the JSON into a string (using JSON.stringify). On the client side, parse the string to convert it into JSON (using JSON.parse) and build your charts and graphs. View the full article

When you make a purchase through PayPal, the payment company sends you an email receipt with the details of the transaction. The Google script will scan your Gmail mailbox for all Paypal receipts, extracts the details from the message body using regex and saves them in a Google Spreadsheet for quick reference. The script extracts the transaction ID, the item purchased, the shipping cost and other details. Also see: Sell Digital Goods with Paypal and Google Drive function searchGmail() { var threads = GmailApp.search("from:paypal", 0, 10); var sheet = SpreadsheetApp.getActiveSheet(); var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; for (var t = 0; t < threads.length; t++) { var msgs = threads[t].getMessages(); for (var m = 0; m < msgs.length; m++) { var response = extractPayPalDetails_(msgs[m]); var row = []; for (var h = 0; h < header.length; h++) { if (header[h] in response) { row.push(response[header[h]]); } else { row.push(""); } } sheet.appendRow(row); } } } function extractPayPalDetails_(msg) { var result = {}; var body = msg.getPlainBody().replace(/\s+/g, " "), html = msg.getBody().replace(/\s+/g, " "); var match = /[A-Z]{3}\s\d+,\s\d{4}\s\d{2}:\d{2}:\d{2}\s.{9}/g.exec(body); if (match) result["Transaction Date"] = match[1]; match = /.*?>(.*?)<\/td>.*?>.*?>(.*?)<\/a><\/td>.*?>(.*?)<\/td>.*?>(.*?)<\/td>.*?>(.*?)<\/td><\/tr>/g.exec( html ); if (match) { result["Item #"] = match[1]; result["Item Title"] = match[2]; result["Quantity"] = match[3]; result["Price"] = match[4]; result["Subtotal"] = match[5]; } match = /Shipping & Handling:\s+\(.*?\)(.*?)\s+Shipping/g.exec(body); if (match) result["Shipping and Handling"] = match[1]; match = /Shipping Insurance.*?:(.*?)\s+Total:\s*(.*? .*?)\s+/g.exec(body); if (match) { result["Shipping Insurance"] = match[1]; result["Total"] = match[2]; } match = /credit card statement as "(.*?)".*?purchased from:(.*?)\s+Receipt id:([\d\-]+)/gi.exec(body); if (match) { result["Name in Statement"] = match[1]; result["Purchased From"] = match[2]; result["Receipt ID"] = match[3]; } match = /international shipping.*?Total:(.*?)\s+.*credit card statement as "(.*?)"/gi.exec(body); if (match) { result["International Shipping Total"] = match[1]; result["International Name in Statement"] = match[2]; } return result; } View the full article

There are many ways to Highlight and Remove Duplicates in Google sheets. You can do it manually using various spreadsheet formulas or you can use Google Apps Script. This script, uploaded by Carl Kranich to the Google Drive Template Directory, finds duplicate rows in the active sheet and colors them red but unlike other methods, here you have the option to find duplicates based on data of specific columns. For instance, if the first column is name and the second is age, you can set the value of CHECK_COLUMNS array as 1,2 and the script will only use these 2 columns to catch the duplicate entries. The columns may be contiguous or noncontiguous. function findDuplicates() { // List the columns you want to check by number (A = 1) var CHECK_COLUMNS = [2, 3, 5, 6]; // Get the active sheet and info about it var sourceSheet = SpreadsheetApp.getActiveSheet(); var numRows = sourceSheet.getLastRow(); var numCols = sourceSheet.getLastColumn(); // Create the temporary working sheet var ss = SpreadsheetApp.getActiveSpreadsheet(); var newSheet = ss.insertSheet('FindDupes'); // Copy the desired rows to the FindDupes sheet for (var i = 0; i < CHECK_COLUMNS.length; i++) { var sourceRange = sourceSheet.getRange(1, CHECK_COLUMNS[i], numRows); var nextCol = newSheet.getLastColumn() + 1; sourceRange.copyTo(newSheet.getRange(1, nextCol, numRows)); } // Find duplicates in the FindDupes sheet and color them in the main sheet var dupes = false; var data = newSheet.getDataRange().getValues(); for (i = 1; i < data.length - 1; i++) { for (j = i + 1; j < data.length; j++) { if (data[i].join() == data[j].join()) { dupes = true; sourceSheet.getRange(i + 1, 1, 1, numCols).setBackground('red'); sourceSheet.getRange(j + 1, 1, 1, numCols).setBackground('red'); } } } // Remove the FindDupes temporary sheet ss.deleteSheet(newSheet); // Alert the user with the results if (dupes) { Browser.msgBox('Possible duplicate(s) found and colored red.'); } else { Browser.msgBox('No duplicates found.'); } } View the full article

A couple of YouTube videos, some simple Google formulas and a Google Spreadsheet - that’s all you need to quickly create a YouTube playlist. It will be an anonymous playlist, not connected to your YouTube channel, and may be a good way to bunch together multiple videos for easy sharing on WhatsApp, Twitter or an email newsletter. Make YouTube Playlists with Google Sheets To get started, open this Google Sheet and put the URLs (links) of YouTube videos in column A (one video per cell, starting with Cell A3). As you paste the video links in cell A, the column B gets populated with the video ID while column C will include a video thumbnail. This helps you double-check that the video URL is actually pointing to the intended video. After you are done writing the video URLs, go to cell A1 (see tutorial) and you’ll find a link to your YouTube playlist ready for sharing with the world. If you add or remove videos from column A, the playlist link will be updated automatically. Simple. You can share the Google sheet with other people and collaborate together to build a YouTube playlist. YouTube Spreadsheet YouTube Playlist Generator - How It Works? When you paste the video URL in column A, the REGEXTRACT formula uses the following regex to extract the ID of the YouTube video. =REGEXEXTRACT(A3, "youtu(?:.*\/v\/|.*v\=|\.be\/|.*?embed\/)([A-Za-z0-9_\-]{11})") Once video ID is available, the IMAGE formula is used to create the video thumbnail for that YouTube video. =IMAGE("https://i3.ytimg.com/vi/"&B3&"/hqdefault.jpg", 4, 80, 120) Now comes the most interesting part - generating the YouTube playlist. Well, that’s a simple URL hack where we concatenate the video IDs with the JOIN() method and make a live link using the HYPERLINK() method. =HYPERLINK("https://www.youtube.com/watch_videos?video_ids="&join(",",B3:B);"Link") Also see: How to Copy YouTube Playlists These YouTube playlists, generated on-the-fly, are not saved in your Google account. You can also build a permanent YouTube playlist where multiple people can collaborate with a Google Form and Google Scripts. Check out this project written by Martin Hawksey. Thank you Tor Halvor Solheim for the idea. View the full article

You have been using Google Analytics to track visitors (or page views) on your website but did you know that the same analytics service can also be used for tracking views inside Google Spreadsheets. You insert a little snippet of tracking code inside your spreadsheet and when someone opens the sheet, that visit will be recorded permanently in your Google Analytics account. Google Analytics provides a JavaScript snippet that can be inserted into web templates for tracking visits. You cannot insert JavaScript inside the cells of a Google Spreadsheet but we can use the IMAGE function combined with some Apps Script to enable tracking inside spreadsheet. The visit is recorded as an “event” and not a “page view” and thus your spreadsheet opens will not artificially inflate your Google Analytics reports. To get started, go to your Google Analytics dashboard and make a note of the Google Analytics tracking ID which is a string like UA-12345-67. This web tutorial explains how you can locate the ID inside your Analytics dashboard. Now open any Google Spreadsheet that you wish to track and go to Tools, Script Editor and copy-paste the following code. This is a custom Google Spreadsheet function that will embed the 1x1 tracking GIF image in our spreadsheets. /** * Track Spreadsheet views with Google Analytics * * @param {string} gaaccount Google Analytics Account like UA-1234-56. * @param {string} spreadsheet Name of the Google Spreadsheet. * @param {string} sheetname Name of individual Google Sheet. * @return The 1x1 tracking GIF image * @customfunction */ function GOOGLEANALYTICS(gaaccount, spreadsheet, sheetname) { /** * Written by Amit Agarwal * Web: www.ctrlq.org * Email: [email protected] */ var imageURL = [ 'https://ssl.google-analytics.com/collect?v=1&t=event', '&tid=' + gaaccount, '&cid=' + Utilities.getUuid(), '&z=' + Math.round(Date.now() / 1000).toString(), '&ec=' + encodeURIComponent('Google Spreadsheets'), '&ea=' + encodeURIComponent(spreadsheet || 'Spreadsheet'), '&el=' + encodeURIComponent(sheetname || 'Sheet') ].join(''); return imageURL; } Save the code, close the Apps Script editor window and return to the spreadsheet. Click an empty cell and insert the following formula. The cell will be blank but it contains an embedded image. You may want to change the background color so it is easy to figure out which cell in the spreadsheet contains the tracking formula. The GOOGLEANALYTICS() formula takes 3 parameters - the analytics ID, the spreadsheet name and the sheet name. This helps if you would like to separately track individual sheets inside a spreadsheet. Now open the spreadsheet in a new browser window and go to Google Analytics, Real Time, Overview to test if the tracking is working. It may sometimes take a minute to record the visit. If you would like to see all the visits, go to Behavior - Events - Overview and click on the Google Spreadsheets category. Also see: Track Gmail Messages with Google Analytics The tracking would work even if the user has enabled ad blocking and that’s because Google Spreadsheets, like Gmail, serves images through a proxy server. The downside is that you will never know the location of the visitor since all visits will be show up as United States (the location of Google servers). View the full article

The Google Script will save the body of email messages from Gmail to the currently active worksheet inside your Google Spreadsheet. You need to specify the Gmail search query and the sheet ID where the matching messages are to be exported. It saves the text content of the message sans any HTML tags or images. To get started, paste the code in the script editor of a Google Spreadsheet and run SaveEmail from the Run menu. Also see: Save Gmail Attachment to Google Drive var SEARCH_QUERY = 'label:inbox is:unread to:me'; /* Credit: Alexander Ivanov https://gist.github.com/contributorpw/70e04a67f1f5fd96a708 */ function getEmails_(q) { var emails = []; var threads = GmailApp.search(q); for (var i in threads) { var msgs = threads[i].getMessages(); for (var j in msgs) { emails.push([ msgs[j] .getBody() .replace(/<.+?>/g, '\n') .replace(/^\s*\n/gm, '') .replace(/^\s*/gm, '') .replace(/\s*\n/gm, '\n'), ]); } } return emails; } function appendData_(sheet, array2d) { sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d); } function saveEmails() { var array2d = getEmails_(SEARCH_QUERY); if (array2d) { appendData_(SpreadsheetApp.getActiveSheet(), array2d); } } View the full article

In most organizations, employees are required to fill timesheets every week so the company can track the amount of time that people have spent working on various projects and tasks. My kids are in middle/high school and I’ve been looking for a timesheet-style solution that would help me understand their studying patterns and learn how much time they spend on various subjects. There are quite a few apps available for time tracking but I was looking for something simple and Google Forms fit the bill perfectly. The multiple-choice grid question is handy for quickly marking the time that they have spent on various subjects. I also added a Linear scale question for them to self-evaluate their daily screen time on a relative scale. Here’s how the final study log form looks like: The Email Form Notifications add-on is enabled for this Google Form so parents get an instant email when the boys submit the study log form. The email contains all the form answers in a neatly formatted table. An instant push notification is sent on the mobile phone as well with IFTTT. Google Forms write all the form responses in a Google Spreadsheet and that makes it even easier to analyze the study pattern visually. You can use one of the available charts or hit the “Explore” button in Google Sheets and ask questions in natural English about the answers stored in your spreadsheet. For instance, you can say “Sum of Subjects [Physics] by Name last week” and it will instantly tell you the amount of time that each kid has spent on that subject last week. Or say “What percentage of Subjects [Physics] is not empty where name is Aryaman” to know the percentage of days when he touched a particular subject. And because Google Forms work just fine on mobile devices, students can quickly fill the time log anywhere as long as they know the form’s URL. View the full article

When a user submits a Google Form, the entry is saved as a new row in the Google Spreadsheet that is collecting the responses. The Timestamp column in the spreadsheet can help you get the Edit Response URL for any entry in the form: // when = e.namedValues["Timestamp"].toString() const getFormResponseURL = (when) => { const formUrl = SpreadsheetApp.getActive().getFormUrl(); const form = FormApp.openByUrl(formUrl); const responseDate = new Date(when); // Gets an array of all of the form's responses after a given date and time. const responses = form.getResponses(responseDate); if (responses.length > 0) { return responses[0].getEditResponseUrl(); } return ''; }; Also see: Get Google Forms Data in Email View the full article

Microsoft Excel provides a handy feature called “Quick Styles” to help you quickly format a selected range as a striped table. The table can have zebra lines meaning alternating rows are formatted with different colors. In Google Sheets, you can use conditional formatting combined with a simple Google Formula to create a table formatting like zebra strips. You can apply alternating colors to both rows and columns in Google Sheets easily. Here’s the trick. Open a Google Sheet and choose Conditional formatting from the Format menu. Select Custom Formula from the dropdown and put this formula in the input box. =ISEVEN(ROW()) Select a Background color for the rule and set the range in A1 notation. For instance, if you wish to apply alternating colors to rows 1 to 100 for columns A to Z, set the range as A1 . Click the “Add another rule” link and repeat the steps but set =ISODD(ROW()) as the custom formula and choose a different background color. Save the rules and the zebra stripes would be automatically applied to the specified range of cells. Tip: If you wish to extend this technique to format columns with different colors, use the =ISEVEN(COLUMN()) formula. Simple! View the full article

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