Ceacer

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

You can easily convert any Google Spreadsheet or Google Document in your Google Drive to other formats like PDF, XLS, etc with Google Apps Script and either email the converted file or save it back to Google Drive. You can get the Email Google Spreadsheet add-on if you prefer the easier route that doesn’t require you to write any Google Apps Script code. Save Google Document as HTML file // Credit Stéphane Giron function exportAsHTML(documentId) { var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested var url = 'https://docs.google.com/feeds/download/documents/export/Export?id=' + documentId + '&exportFormat=html'; var param = { method: 'get', headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }, muteHttpExceptions: true, }; var html = UrlFetchApp.fetch(url, param).getContentText(); var file = DriveApp.createFile(documentId + '.html', html); return file.getUrl(); } Export Google Spreadsheet as Microsoft Excel format // Credit: Eric Koleda function exportAsExcel(spreadsheetId) { var file = Drive.Files.get(spreadsheetId); var url = file.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']; var token = ScriptApp.getOAuthToken(); var response = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token, }, }); return response.getBlob(); } View the full article

This tutorial explains how to make phone numbers clickable within Google Sheets, Slides and Google Docs. When someone clicks on the phone number link in your spreadsheet or this document, it will open the dialer on their mobile phone and initiate dialing of the specified phone number. How to Insert Clickable Phone Numbers in Web Pages Let’s start with the basics. If you click an email link on a webpage, it opens your default mail program. Similarly, you can make phone numbers on your website “callable” meaning when someone clicks on the phone number link, it will automatically launch the dialer on their mobile phone with the phone number filled in. You can use the tel protocol to convert a plain text phone number on a web page into a clickable telephone link. For instance, if you click this link on a mobile phone, it will open the phone dialer prefilled with the specified number. There’s no need to copy-paste numbers. How to Type Phone Numbers in Google Sheets It is a bit tricky to type phone numbers inside Google Spreadsheets. Here’s why: Phone numbers typically consist of digits preceded by the plus (+) symbol. However, a common issue is that when you include the plus sign in a cell, the spreadsheet assumes you are entering a math formula and attempts to calculate the value. If you encounter this problem, there are two easy ways to resolve it. Workaround A You can surround the phone number with double quotes (”) and precede it with an equal sign (=). Workaround B You can add a single quote (’) before the phone number. This tells Google Sheets to treat the cell’s contents as text, preserving the formatting of the phone number. How to Make Phone Numbers Clickable in Google Sheets Coming to the main problem, how do you make phone numbers inside a Google Sheet clickable? The obvious choice would be to use the HYPERLINK formula with the tel protocol but it is not supported inside Google Sheets. So a formula like =HYPERLINK("tel:12345", "Call Me") would not work but there’s a simple workaround to this issue. Append the phone number with the call.ctrlq.org domain name and it will automatically convert the phone number into a clickable link. For example, if you want to create a clickable phone link for the number +1 650-253-0000, you can use the following formula. You can create a regular hyperlink in the cell pointing to a website which in turn redirects to the actual telephone link. To see this in action, add https://call.ctrlq.org/ before any phone number in the Google Sheet and it will turn into a callable phone link. =HYPERLINK("https://call.ctrlq.org/+16502530000"; "Call Google Support") In the above example, the phone numbers are listed in column B while the names are in column A. You can add the following formula in column C to have clickable phone links. =HYPERLINK("https://call.ctrlq.org/"&B2; A2) You may open this Phone Number Google Sheet on your Android or iPhone and click on any of the phone links to see it in action. You can even publish the sheet as a web page and the numbers will be clickable on the web too. Clickable Phone Numbers in Google Docs and Slides You can also create clickable phone numbers in Google Docs and Google Slides. The process is similar to Google Sheets but we’ll use the Insert Link option instead of the HYPERLINK formula. Write the phone number inside the document and select it. Then click on the Insert menu and select Link from the dropdown. Or you can use the keyboard shortcut Ctrl+K to open the link dialog. Enter the phone number preceded by the call.ctrlq.org domain name and click on the OK button. The phone number will be converted into a clickable link. Also see: Add Images in Google Spreadsheets The Technical Details The call.ctrlq.org service is a simple Node.js app running on Google Cloud Run that merely redirects to the tel protocol. Here’s the entire app code should you want to run it on your own server. const express = require('express'); const app = express(); app.get('/:number', (req, res) => { const { number } = req.params; const phone = number.replace(/[^0-9]/g, ''); res.redirect(`tel:${phone}`); }); app.listen(process.env.PORT, () => { console.log(`App is running`); }); View the full article

Conditional content allows you to customize your Google Docs template and generate different versions of the same document based on the user’s answers. In this tutorial, I’ll show you how to use conditional content in Google Docs using Document Studio, a Google add-on that automates document creation. If you are new here, please follow this step-by-step guide on how to generate documents from data in Google Sheets and Google Forms responses. Conditionally Display Content Let’s say you are a recruiter who wants to use a Google Docs template to send out job offer letters to candidates. You want to include specific information in the offer letter based on the candidate’s job title and location. The conditional statements that we would like to include in the document template are: Your office is located in San Francisco. Your offer letter should include a paragraph offering relocation benefits only to candidates who are located outside SF. If the candidate is offered an Associate position, they are eligible for basic benefits. If the candidate is hired for a senior position, like Manager or Director, they are entitled to additional benefits like 401(k) retirement plan. Define the conditional sections Create a new Google Docs document and create a job offer letter template. Include sections for the candidate’s name, job title, location, salary, and benefits package. Use the <> and <> expressions to define the conditional sections in your template. For example, you might use the following expressions to show or hide the relocation package section based on the candidate’s location: <<If: ({{Location}} != 'San Francisco')>> We are pleased to offer you a relocation package to assist with your move from {{Location}} to our main office. <<EndIf>> Similarly, you can wrap the benefits paragraph with the <> and <> expressions to show or hide the benefits package section based on the candidate’s job title: <<If: OR (({{Job Title}} == 'Manager'), ({{Job Title}} == 'Director'))>> As {{Job Title}}, you will be eligible for our comprehensive benefits package, which includes health, dental, and vision insurance, a 401(k) retirement plan, and more. <<EndIf>> You may also use the ~ contains operator in place of == equals operator for partial matches. For instance, {{Job Title}} ~ 'Manager' will match Sales Manager, Senior Manager, Manager and so on. Here’s how the final job offer letter template looks like. Play ; In addition to document templates, you can also add conditional text in email templates with the help of scriptlets. Things to know: You may not nest IF statements inside each other. You can use the OR, AND or NOR operator to combine multiple conditions. If you have a table in your document that should be displayed conditionally, you should put the wrapping <> and <> tags outside the table. It is currently not possible to hide or show specific rows or columns of a table based on the user’s answers. View the full article

Whether you are looking to learn a programming language, enhance your Microsoft Excel skills, or acquire knowledge in Machine Learning, Udemy probably has a video course for you. Udemy courses are usually affordable, there are no subscription fee and you can learn at your own pace. Free Udemy Courses on Programming While most video tutorials on Udemy require payment, the website also offers some of their highly-rated courses for free. I’ve prepared a Google Sheet that lists all the free programming courses currently available on Udemy. The spreadsheet is updated automatically every few hours. You can also access the web version for easy browsing. ✨ You may use the search function of the browser (Ctrl + F) to find courses for a specific programming language or topic. The courses are sorted by popularity. There’s no secret sauce. Udemy has an developer API that provides access to all the course data available on the website, including user ratings, number of students who have taken the course, duration, preview video lectures, and more. Use the Udemy API with Google Sheets The Udemy API is free to use but requires authentication. You can generate the credentials for your Udemy account and then use the /courses endpoint to fetch the list of free courses. const parseCourseData_ = (courses) => courses .filter( ({ is_paid, primary_category }) => is_paid === false && ['Development', 'IT & Software'].includes(primary_category.title) // We are primarily interested in programming courses on Udemy ) .map((e) => [ `=IMAGE("${e.image_240x135}")`, `=HYPERLINK("https://www.udemy.com${e.url}";"${e.title}")`, e.visible_instructors.map(({ display_name }) => display_name).join(', '), e.num_subscribers, Math.round(e.avg_rating * 100) / 100, e.num_reviews, e.content_info_short, e.num_lectures, new Date(e.last_update_date) ]); const listUdemyCoursesGoneFree = () => { // Put your Udemy credentials here const CLIENT_ID = ''; const CLIENT_SECRET = ''; const params = { page: 1, page_size: 100, is_paid: false, 'fields[course]': '@all' }; const query = Object.entries(params) .map(([key, value]) => `${key}=${encodeURIComponent(value)}`) .join('&'); const apiUrl = `https://www.udemy.com/api-2.0/courses/?${query}`; const bearer = Utilities.base64Encode(`${CLIENT_ID}:${CLIENT_SECRET}`); const options = { muteHttpExceptions: true, headers: { Authorization: `Basic ${bearer}` } }; const courses = []; do { const response = UrlFetchApp.fetch(apiUrl, options); const { results = [], next } = JSON.parse(response); courses.push(...parseCourseData_(results)); url = next; } while (url && courses.length < 500); const ss = SpreadsheetApp.getActiveSpreadsheet(); const [sheet] = ss.getSheets(); sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent(); sheet.getRange(2, 1, courses.length, courses[0].length).setValues(courses); }; We use the UrlFetch service of Google Scripts to fetch the data from the Udemy API and the data is then parsed and inserted into the Google Sheet. The course thumbnail image is rendered using the IMAGE formula and the course title is linked to the Udemy website using the HYPERLINK formula. Related reading: The Best Websites to Learn to Coding Online The Best Online Teachers for Learning Web Development Read this before buying a Udemy Course View the full article

This Google Spreadsheet on Udemy courses has about 50 sheets, one for each programming language, and the sheets are sorted in random order so it is difficult to find a specific sheet. It will take a while to sort the worksheets manually but we can easily automate the process with Google Apps Script and easily navigate through large spreadsheets. Automate Sheet Sorting with Google Apps Script The following code snippet will automatically sort the worksheets in a Google Sheet alphanumerically. The script can arrange the sheets in either ascending or descending order based on the sheet names. To get started, go to Extensions > Apps Script to open the script editor. Then, copy and paste the following code: const sortGoogleSheets = (ascending = true) => { const options = { sensitivity: "base", ignorePunctuation: true, numeric: true, }; const compareFn = (sheet1, sheet2) => { return ascending ? sheet1.getName().localeCompare(sheet2.getName(), undefined, options) : sheet2.getName().localeCompare(sheet1.getName(), undefined, options); }; // Get the active spreadsheet. const ss = SpreadsheetApp.getActiveSpreadsheet(); ss.getSheets() .sort(compareFn) .reverse() .forEach(sheet => { ss.setActiveSheet(sheet); ss.moveActiveSheet(1); }); // Flush the changes to the spreadsheet. SpreadsheetApp.flush(); }; The compareFn function compares two sheets and returns a value that indicates whether the first sheet should come before or after the second sheet. The function returns the following values: -1 if the first sheet should come before the second sheet. 1 if the first sheet should come after the second sheet. Advanced Sort Options const options = { sensitivity: "base", ignorePunctuation: true, numeric: true, }; The options object specifies the options for the locale comparison. Here are some important things to know: The numeric property specifies whether numbers should be treated as numbers instead of strings. If this property is set to false, “Sheet1” and “Sheet10” will come before “Sheet2”. The ignorePunctuation property specifies whether spaces, brackets and other punctuation should be ignored during the comparison. If this property is set to false, “Sheet 1” and “Sheet1” will be treated as different sheets. The sensitivity property specifies if the comparison should be case-sensitive or case-insensitive. Set this property to “accent” to treat base letters and accented characters differently (Sheet a and Sheet à will be treated as different sheets). Sort Google Sheets by Date If your sheet names contain dates, like “March 2023” or “01/03/23”, you’ll need to convert the dates to numbers before comparing them. const compareFn = (sheet1, sheet2) => { return ascending ? new Date(sheet1.getName()).getTime() - new Date(sheet2.getName()).getTime() : new Date(sheet2.getName()).getTime() - new Date(sheet1.getName()).getTime(); }; References localeCompare() method Intl.Collator API View the full article

The Save Gmail to Google Drive add-on lets you automatically download email messages and file attachments from Gmail to your Google Drive. You can save the email messages as PDF while the attachments are saved in their original format. Transcribe Gmail Attachments The latest version of the Gmail add-on adds support for transcribing audio and video attachments in Gmail messages. The transcription is done with the help of OpenAI’s Whisper API and the transcript is saved as a new text file in your Google Drive. Here’s a step by step guide on how you can transcribe audio and video attachments in Gmail messages to text. Step 1. Install the Save Gmail to Google Drive add-on from the Google Workspace marketplace. Open sheets.new to create a new Google Sheet. Go to the Extension menu > Save Emails > Open App to launch the add-on. Step 2. Create a new workflow and specify the Gmail search criteria. The add-on will scan the matching email message for any audio and video files. OpenAI’s speech-to-text API supports a wide range of audio and video formats including MP3, WAV, MP4, MPEG, and WEBM. The maximum file size is 25 MB and you’ll always be in the limit since Gmail doesn’t allow you to send or receive files larger than 25 MB. Step 3. On the next screen, check the option that says Save Audio and Video Attachments as text and choose the file format, text or PDF, in which you would like to save the transcript. You can include markers in the file name. For instance, if you specify the file name as {{Subject}} {{Sender Email}}, the add-on will replace the markers with the actual sender’s email and the email subject. You would also need to specify the OpenAI API key that you can get from the OpenAI dashboard. OpenAI charges you $0.006 per minute of audio or video transcribed, rounded to the nearest second. Save the workflow and it will automatically run in the background, transcribing messages as they land in your inbox. You can check the status of the workflow in the Google Sheet itself. Also see: Speech to Text with Dictation.io Speech to Text with Google Apps Script Internally, the add-on uses the Google Apps Script to connect to the OpenAI API and transcribe the audio and video files. Here’s the source code of the Google Script that you can copy and use in your own projects. // Define the URL for the OpenAI audio transcription API const WHISPER_API_URL = 'https://api.openai.com/v1/audio/transcriptions'; // Define your OpenAI API key const OPENAI_API_KEY = 'sk-putyourownkeyhere'; // Define a function that takes an audio file ID and language as parameters const transcribeAudio = (fileId, language) => { // Get the audio file as a blob using the Google Drive API const audioBlob = DriveApp.getFileById(fileId).getBlob(); // Send a POST request to the OpenAI API with the audio file const response = UrlFetchApp.fetch(WHISPER_API_URL, { method: 'POST', headers: { Authorization: `Bearer ${OPENAI_API_KEY}` }, payload: { model: 'whisper-1', file: audioBlob, response_format: 'text', language: language } }); // Get the transcription from the API response and log it to the console const data = response.getContentText(); Logger.log(data.trim()); }; Please replace the OPENAI_API_KEY value with your own OpenAI API key. Also, make sure that the audio or video file you want to transcribe is stored in your Google Drive and that you have at least view (read) permissions on the file. Transcribe Large Audio and Video Files The Whisper API only accepts audio files that are less than 25 MB in size. If you have a larger file, you can use the Pydub Python package to split the audio file into smaller chunks and then send them to the API for transcription. If the video file is large in size, you may extract the audio track from the video file using FFmpeg and send that to the API for transcription. # Extract the audio from video ffmpeg -i video.mp4 -vn -ab 256 audio.mp3 ## Split the audio file into smaller chunks ffmpeg -i large_audio.mp3 -f segment -segment_time 60 -c copy output_%03d.mp3 FFmpeg will split the input audio file into multiple 60-second chunks, naming them as output_001.mp3, output_002.mp3, and so on, depending on the duration of the input file. View the full article

You can bring the power of Google Maps to your Google Sheets using simple formulas with no coding. You don’t need to sign-up for the Google Maps API and all results from Google Maps are cached in the sheet so you are unlikely to hit any quota limits. To give you a quick example, if you have the starting address in column A and the destination address in column B, a formula like =GOOGLEMAPS_DISTANCE(A1, B1, "driving") will quickly calculate the distance between the two points. Or modify the formula slightly =GOOGLEMAPS_TIME(A1, B1, "walking") to know how long it will take for a person to walk from one point to another. If you would like to try the Google Maps formulas without getting into the technical details, just make a copy of this Google Sheet and you are all set. Using Google Maps inside Google Sheets This tutorial explains how you can easily write custom Google Maps functions inside Google Sheets that will help you: Calculate distances between two cities or any addresses. Calculate the travel time (walking, driving or biking) between two points. Get the latitude and longitude co-ordinates of any address on Google Maps. Use reverse geocoding to find the postal address from GPS co-ordinates. Print driving directions between any points on earth. Get the address from the zip code itself. 1. Calculate Distances in Google Sheets Specify the origin, the destination, the travel mode (walking or driving) and the function will return the distance between the two points in miles. =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking") /** * Calculate the distance between two * locations on Google Maps. * * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The distance in miles * @customFunction */ const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => { const { routes: [data] = [] } = Maps.newDirectionFinder() .setOrigin(origin) .setDestination(destination) .setMode(mode) .getDirections(); if (!data) { throw new Error('No route found!'); } const { legs: [{ distance: { text: distance } } = {}] = [] } = data; return distance; }; 2. Reverse Geocoding in Google Sheets Specify the latitude and longitude and get the full address of the point through reverse geocoding of coordinates. =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking") /** * Use Reverse Geocoding to get the address of * a point location (latitude, longitude) on Google Maps. * * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude) * * @param {String} latitude The latitude to lookup. * @param {String} longitude The longitude to lookup. * @return {String} The postal address of the point. * @customFunction */ const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => { const { results: [data = {}] = [] } = Maps.newGeocoder().reverseGeocode(latitude, longitude); return data.formatted_address; }; 3. Get the GPS coordinates of an address Get the latitude and longitude of any address on Google Maps. =GOOGLEMAPS_LATLONG("10 Hanover Square, NY") /** * Get the latitude and longitude of any * address on Google Maps. * * =GOOGLEMAPS_LATLONG("10 Hanover Square, NY") * * @param {String} address The address to lookup. * @return {String} The latitude and longitude of the address. * @customFunction */ const GOOGLEMAPS_LATLONG = (address) => { const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address); if (data === null) { throw new Error('Address not found!'); } const { geometry: { location: { lat, lng } } = {} } = data; return `${lat}, ${lng}`; }; 4. Print the driving directions between addresses Specify the origin address, the destination address, the travel mode and the function will use the Google Maps API to print step-by-step driving directions. =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking") /** * Find the driving direction between two * locations on Google Maps. * * =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The driving direction * @customFunction */ const GOOGLEMAPS_DIRECTIONS = (origin, destination, mode = 'driving') => { const { routes = [] } = Maps.newDirectionFinder() .setOrigin(origin) .setDestination(destination) .setMode(mode) .getDirections(); if (!routes.length) { throw new Error('No route found!'); } return routes .map(({ legs }) => { return legs.map(({ steps }) => { return steps.map((step) => { return step.html_instructions.replace(/<[^>]+>/g, ''); }); }); }) .join(', '); }; 5. Measure the trip time with Google Maps Specify the origin address, the destination address, the travel mode and the function will measure your approximate trip time between the specified addresses, provided a route exists. =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking") /** * Calculate the travel time between two locations * on Google Maps. * * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The time in minutes * @customFunction */ const GOOGLEMAPS_DURATION = (origin, destination, mode = 'driving') => { const { routes: [data] = [] } = Maps.newDirectionFinder() .setOrigin(origin) .setDestination(destination) .setMode(mode) .getDirections(); if (!data) { throw new Error('No route found!'); } const { legs: [{ duration: { text: time } } = {}] = [] } = data; return time; }; Tip: Improve Formula Performance by Caching The Google Sheets functions internally use the Google Maps API to calculate routes, distances and travel time. Google offers a limited quota for Maps operations and if your sheet performs too many queries in a short duration, you are likely to see errors like ""Service invoked too many times for one day” or something similar. To get around the quota issue, it is recommended that you use Apps Script’s built-in cache to store results and, if the results of a function already exist in the case, you’ll make one less request to Google Maps. The Maps functions inside this Google Sheet also use caching and here’s how you can implement it. // The cache key for "New York" and "new york " should be same const md5 = (key = '') => { const code = key.toLowerCase().replace(/\s/g, ''); return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key) .map((char) => (char + 256).toString(16).slice(-2)) .join(''); }; const getCache = (key) => { return CacheService.getDocumentCache().get(md5(key)); }; // Store the results for 6 hours const setCache = (key, value) => { const expirationInSeconds = 6 * 60 * 60; CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds); }; /** * Calculate the travel time between two locations * on Google Maps. * * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The time in minutes * @customFunction */ const GOOGLEMAPS_DURATION = (origin, destination, mode = 'driving') => { const key = ['duration', origin, destination, mode].join(','); // Is result in the internal cache? const value = getCache(key); // If yes, serve the cached result if (value !== null) return value; const { routes: [data] = [] } = Maps.newDirectionFinder() .setOrigin(origin) .setDestination(destination) .setMode(mode) .getDirections(); if (!data) { throw new Error('No route found!'); } const { legs: [{ duration: { text: time } } = {}] = [] } = data; // Store the result in internal cache for future setCache(key, time); return time; }; Also see: Embed Google Maps in Emails and Documents View the full article

The Email Spreadsheets add-on for Google Sheets can save office workers a ton of time by automating the reporting of spreadsheet data and dashboards by email. With this add-on, you can schedule reports to be sent automatically on a recurring schedule, so you don’t have to manually email spreadsheets to colleagues anymore. Play ; With Email Spreadsheets, you can schedule reports and it will automatically send them by email on a recurring schedule. You can email entire workbooks, specific sheets inside a workbook or even range of cells. Watch the video to get started. And because the add-on runs on the Google Cloud, your spreadsheet reports will be delivered even while you are offline or on vacation. Email Google Sheets Automatically For this example, our Google Spreadsheet has two sheets - the first sheet contains a data table and the second sheet contains an image chart with a neatly formatted table. We’ll build a scheduled workflow that will email the sheets data, including charts, on the first Monday of every week. Step 1: Select Sheets to Export Install the Email Google Sheets addon from Google marketplace. Next, open any Google Spreadsheet in your Google Drive, go to the Extensions menu inside the sheet, choose Email Spreadsheets from the dropdown. Click Open to launch the app and click the Create Workflow button to create your first scheduled email report. You’ll be presented with a list of sheets available in the current workbook. Select one or more sheets that you would like to send with the scheduled email. You may export sheets in PDF, Excel, CSV or a PNG image. Each sheet is attached as a separate file in the email but you can choose the “Entire Workbook” option to create a single file from all sheets in the workbook. You may also use dynamic markers to customize the file name of the exported files. For instance, the marker {{Sheet Name}} {{Day}}-{{Month}} will append the current date and month to the exported sheet name. Tip: If your Google Sheet table is large, you can specify the cell range in A1 notation (like A1 ) and only the specified range would be exported. Step 2: Custom PDF Export Settings The Email Google Sheets addon lets you customize the PDF layout that is exported from Google Sheets. You can change the paper orientation (Portrait or Landscape), the paper size or alter the print margins to fit more content on a page. You can choose to show gridlines, notes, sheet names and page numbers in the exported file. Step 3: Write the Email Template Next, we create an email template that will be sent with your reports. You can specify one or email recipients in the TO, CC, or BCC fields. You can also specify dynamic email recipients based on cell values in the spreadsheet. For instance, if the email address of the recipient is specified in cell B2 of a sheet titled “Employee Shifts”, you can put {{Employee Shifts!B2}} in the To field, and the add-on will pull the dynamic value from the cell at the time of sending the email report. These dynamic cell values enclosed inside double curly braces can be used inside any of the email fields including subject, email body, and the sender’s name. The email body can include dynamic cell values as well as ranges that make it easy of you to send portions of the spreadsheet without sharing the full workbook. For instance, you can write {{Employee Wages!B2:F9}} to include only the specific range (B2 ) from the Wages sheet. Internally, the add-on converts the range to an HTML table, retaining all the display formatting with CSS, and embed it into the email. Charts and Timelines can be embedded into the email body using a special {{ Chart }} marker - you can find these markers inside the markers dropdown of the email editor. Business can also add their own logo and signature in the email body. Tip: Use the Test Email button to send an email with the exported files before setting up the schedule. Step 4: Setup the Email Schedule The Email Google Sheets add-on includes an email scheduler to help you set up recurring schedules visually. You can schedule and send emails hourly, daily, weekly, monthly or even on a yearly recurring basis. It is also possible to exclude dates and your spreadsheet won’t be emailed on the specified dates. That’s it. Save the workflow and it will be activated instantly. You can also schedule multiple emails from the same Google Spreadsheet by adding more workflows. The Email Spreadsheets add-on is a powerful tool that can help you automate the reporting of spreadsheet data and dashboards by email. To learn more about the Email Spreadsheets add-on and to download it, please visit the Google Workspace Marketplace. ️Download Email Sheets Email Google Sheets - How it works? The add-on is written in Google Apps Script. It uses the Google Sheets API to convert sheets to PDF files and uses the Gmail API for sending the converted files as attachments. View the full article

Bob Canning writes: I have a Google Spreadsheet with postal addresses in column A. Each week, a real estate agent copies a section of those addresses to a “upcoming tour” tab on our website. The tab is shared with other real estate agents so they can see the addresses in the order they will be viewed on the tour. I would like to make all of the addresses clickable so that people can easily navigate to the next location on the tour. Is this possible? Make Addresses Clickable in Google Sheets We can use custom functions in Google Sheets with the built-in HYPERLINK function to make any location clickable in the spreadsheet. And unlike other Google Maps functions, this approach doesn’t make any Maps API calls so there’s no restriction on the number of links that you can generate in a sheet. Assuming that your postal addresses are in column A from row 2 to row 11, go to column B and paste the custom function. The first parameter refers to the cell, or range of cells, that contain the location that needs to be hyperlinked. You can set the second ‘satellite’ parameter to TRUE if you would like to link the map to the aerial view instead of the regular map view of Google Maps. =GOOGLEMAPSLINK(A2:A11, FALSE) The Google Maps Link function is obviously not part of Google Sheets but we can easily integrate it with the help of Google Apps Script. Generate Maps URL with Apps Script Open your Google Sheets spreadsheet. Click on “Extensions” in the top menu, then select “Apps Script.” In the Apps Script editor that opens, replace any existing code with the following function: /** * Generate a Google Maps Link for any address * * @param {string} address - The postal address * @param {boolean} satellite - Show aerial view (TRUE or FALSE) * @returns {string} The Google Maps URL * @customFunction */ function GOOGLEMAPSLINK(address, satellite) { function createLink(query) { const baseUrl = 'https://maps.google.com/?q=' + encodeURIComponent(query); const mapsUrl = baseUrl + (satellite ? '&t=k' : ''); return mapsUrl; } return Array.isArray(address) ? address.map(createLink) : createLink(address); } The GOOGLEMAPSLINK function can generate map links for addresses in a single cell as well as a range of cells. We can also add another column to the sheet that will create a clickable link with the address text. Paste the following ArrayFormula function in cell C1. See demo sheet. =ArrayFormula(HYPERLINK(B2:B11,A2:A11)) The hyperlinked postal addresses can also be copied and pasted directly into Word, or any rich text editor, including HTML Mail for Gmail. View the full article

The availability of third-party add-ons for Google Docs, Sheets and Google Slides have certainly made the Google productivity suite more capable and useful. If you haven’t tried them yet, open any Google document or spreadsheet in your Google Drive and look for the extensions menu near Help. Google Workspace users may have to ask their admin to enable support for add-ons for the organization. For starters, Google add-ons are like extensions for Chrome. Extensions add new features to the Chrome browser and add-ons extend the functionality of Google Office applications. Anyone can write a Google add-on with some basic programming knowledge for writing HTML and CSS for styling the add-on. The server side code is written in Google Apps Script which is similar to JavaScript but runs on the Google Cloud. Google Apps Script vs Google Add-ons Google Add-ons are written in the Google Apps Script language but while regular Google Scripts can work on any document in your Google Drive, add-ons only work against the document or sheet that’s currently open in your browser. The other big difference is that you can view the source code of regular Google Scripts while in the case of add-ons, the code is hidden from the end user. This helps developers protect their code but a downside is that the user has no clue about what’s happening behind the scenes. We have seen issues with Chrome extensions and add-ons for Google Docs can be a target as well. For instance, an add-on can possibly email a copy of the current document or sheet to another email address? Or maybe it can share a folder in Google Drive with someone else. The good part is that add-ons listed in Google Workspace have been tested and reviewed by Google and, they go through a security review process if it requires access to sensitive scopes (like sending Gmail or accessing Google Drive). The Best Add-ons For Google Docs, Sheets and Google Slides The Google Workspace marketplace lists hundreds of Google add-ons and here are some of favorite ones that you should have in your Google Docs and Sheets. The are compatible with both GSuite and consumer Google accounts. Mail Merge for Gmail - Send personalized emails with emails to multiple email recipients with Mail Merge for Gmail and Google Workspace. Document Studio - Generate certificates, invoices, and other documents automatically from source data in Google Sheets or Google Form submissions. Download Gmail Emails - Download your Gmail messages and attachments in Google Drive for archiving. Email Scheduler for Gmail - Schedule emails inside Gmail for sending later at a specific date and time. Send repetitive emails that go on a recurring schedule. Google Drive Permissions Auditor - Know who has access to your files in Google Drive. Gmail Address Extractor - The add-on extracts the email addresses from the header and body of email messages for preparing a mailing list. Creator Studio for Google Slides - Convert Google Slides presentations to animated GIF and MP4 movies. Notifications for Google Forms - Get Google Forms responses in an email message when people submit your forms. Send customized email notifications to respondents. Email Google Spreadsheet as PDF - Convert and email Google Spreadsheets as PDF, Excel or CSV to multiple people. Email sheets manually or on a recurring schedule. Bulk Gmail Forward - Easily forward one or more email threads from Gmail to any other address. Related tutorial: How to Create a Google Docs Add-on View the full article

Do you have image files in your Google Drive with generic names like IMG_123456.jpg or Screenshot.png that offer no context about what the image is about? Wouldn’t it be nice if you had an assistant that could look at these images and automatically suggest descriptive filenames for the images? Rename Files in Google Drive with AI Well, you can use Google’s Gemini AI and Google Apps Script to automatically rename your files in Google Drive in bulk with a descriptive name based on the image content. The following example uses Google’s Gemini AI but the steps can be easily adapted to OpenAI’s GPT-4 Vision or other AI models. To get started, open script.new to create a new Google Script and copy-paste the following code snippets in the editor. You may also want to enable the Advanced Drive API from the Google Script editor under the Resources menu. 1. Get the list of files in a folder The first step is to get the list of files in a folder. We will use the Drive.Files.list method to get the list of files in a folder. The search query contains the mimeType parameter to filter the results and only return Drive files that are image formats supported by the Google Gemini AI. const getFilesInFolder = (folderId) => { const mimeTypes = ['image/png', 'image/jpeg', 'image/webp']; const { files = [] } = Drive.Files.list({ q: `'${folderId}' in parents and (${mimeTypes.map((type) => `mimeType='${type}'`).join(' or ')})`, fields: 'files(id,thumbnailLink,mimeType)', pageSize: 10 }); return files; }; 2. Get the file thumbnail as Base64 The files returned by the Drive.Files.list method contain the thumbnailLink property that points to the thumbnail image of the file. We will use the UrlFetchApp service to fetch the thumbnail image and convert it into a Base64 encoded string. const getFileAsBase64 = (thumbnailLink) => { const blob = UrlFetchApp.fetch(thumbnailLink).getBlob(); const base64 = Utilities.base64Encode(blob.getBytes()); return base64; }; 3. Get the suggested filename from Google Gemini AI We’ll use the Google Gemini API to analyze the visual content of an image and suggest a descriptive filename for the image. Our text prompt looks something like this: Analyze the image content and propose a concise, descriptive filename in 5-15 words without providing any explanation or additional text. Use spaces for file names instead of underscores. You’d need an API key that you can generate from Google AI Studio. const getSuggestedFilename = (base64, fileMimeType) => { try { const text = `Analyze the image content and propose a concise, descriptive filename in 5-15 words without providing any explanation or additional text. Use spaces instead of underscores.`; const apiUrl = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro-vision:generateContent?key=${GEMINI_API_KEY}`; const inlineData = { mimeType: fileMimeType, data: base64 }; // Make a POST request to the Google Gemini Pro Vision API const response = UrlFetchApp.fetch(apiUrl, { method: 'POST', headers: { 'Content-Type': 'application/json' }, payload: JSON.stringify({ contents: [{ parts: [{ inlineData }, { text }] }] }) }); // Parse the response and extract the suggested filename const data = JSON.parse(response); return data.candidates[0].content.parts[0].text.trim(); } catch (f) { return null; } }; 4. Automatically rename files in Google Drive The final step is to put all the pieces together. We will get the list of files in a folder, fetch the thumbnail image of each file, analyze the image content with Google Gemini AI, and rename the file in Google Drive with the suggested filename. const renameFilesInGoogleDrive = () => { const folderId = 'Put your folder ID here'; const files = getFilesInFolder(folderId); files.forEach((file) => { const { id, thumbnailLink, mimeType } = file; const base64 = getFileAsBase64(thumbnailLink); const name = getSuggestedFilename(base64, mimeType); Drive.Files.update({ name }, id); }); }; Google Scripts have a 6-minute execution time limit but you can setup a time-drive trigger so that the script runs automatically at a specific time interval (say every 10 minutes). You may also extend the script to move the files to a different folder after renaming them so that they are not processed again. The full source code is available on GitHub View the full article