CodeCanyon

A client wants to receive email notifications as soon as someone edits a Google Spreadsheet that is shared with a team of people. Google Docs supports the onEdit() trigger that runs whenever an edit is made to any cell of the sheet but a limitation is that the onEdit trigger cannot send emails. Nor can be used to call external API though the URLFetch service. As a workaround, the edits were stored as a Property and another time-based trigger would periodically send the stored value by email. /** * @OnlyCurrentDoc */ function onEdit(e) { var key = "ROWCHANGES"; var range = e.range; var date = Utilities.formatDate(new Date(), e.source.getSpreadsheetTimeZone(), "dd-MM-yy HH:MM:s"); var properties = PropertiesService.getUserProperties(); var sheet = e.source.getActiveSheet(); var data = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0]; data[range.getColumn() - 1] = "" + data[range.getColumn() - 1] + ""; var edits = { name: sheet.getSheetName(), data: data }; var existing = JSON.parse(properties.getProperty(key)) || {}; existing[date] = edits; properties.setProperty(key, JSON.stringify(existing)); } function onEdit_Email() { var properties = PropertiesService.getUserProperties(); var json = JSON.parse(properties.getProperty("ROWCHANGES")); var html = "": for (var keys in json) { html = html + " [" + keys + "][" + json[keys].name + "] — " + json[keys].data; } if (html !== "") { MailApp.sendEmail(email, subject, "", { htmlBody: html }); properties.deleteAllProperties(); } } View the full article

Google Spreadsheets include an import features to help you copy subsheets from another spreadsheet into the currently open sheet. If you however need to merge multiple sheets, Google Scripts can help. Put them all in one folder and run a script that will create a master sheet will all the sheets pulled from other sheets. function mergeSheets() { /* Retrieve the desired folder */ var myFolder = DriveApp.getFoldersByName(SOURCE).next(); /* Get all spreadsheets that resided on that folder */ var spreadSheets = myFolder.getFilesByType('application/vnd.google-apps.spreadsheet'); /* Create the new spreadsheet that you store other sheets */ var newSpreadSheet = SpreadsheetApp.create('Merged Sheets'); /* Iterate over the spreadsheets over the folder */ while (spreadSheets.hasNext()) { var sheet = spreadSheets.next(); /* Open the spreadsheet */ var spreadSheet = SpreadsheetApp.openById(sheet.getId()); /* Get all its sheets */ for (var y in spreadSheet.getSheets()) { /* Copy the sheet to the new merged Spread Sheet */ spreadSheet.getSheets()[y].copyTo(newSpreadSheet); } } } View the full article

A recent project involved pulling payments, invoices and accounting data from QuickBooks online into a Google Spreadsheet in near real-time. The integration was done through Google Apps Script and the QuickBooks API (v3). You also need to include OAuth 1.0 library in your Google Script project (QBO doesn’t support the OAuth 2.0 protocol yet). To get started, go to your QuickBooks Sandbox, create a sample app and get the Consumer Key and Consumer Secret. Next authorize the connection to let Google Sheets access your company inside QuickBooks. The companyId will be stored as a property inside Google Scripts and all subsequent API calls will be made for the authorized company. Here’s a sample snippet that fetches the invoices data from QuickBooks into a Google Spreadsheet. We’ve added a filter in the SELECT query to only fetch invoices that were created in the last hour. You can set this is a time-based trigger to auto-fetch QuickBooks data into the spreadsheet. function getInvoicesFromQuickBooks() { try { var service = getQuickBooksService_(); if (!service || !service.hasAccess()) { Logger.log('Please authorize'); return; } var props = PropertiesService.getUserProperties(), companyId = props.getProperty('QuickBooks.companyID'); var date = new Date(new Date().getTime() - 1000 * 60 * 60).toISOString(); var query = "SELECT * FROM Invoice WHERE Metadata.CreateTime > '" + date + "'"; var url = 'https://quickbooks.api.intuit.com/v3/company/'; url = +companyId + '/query?query=' + encodeURIComponent(query); var response = service.fetch(url, { muteHttpExceptions: true, contentType: 'application/json', headers: { Accept: 'application/json', }, }); var result = JSON.parse(response.getContentText()); var invoices = result.QueryResponse.Invoice; for (var i = 0; i < invoices.length; i++) { var Invoice = invoices[i]; sheet.appendRow([ Invoice.Id, Invoice.time, Invoice.Deposit, Invoice.DocNumber, Invoice.DepartmentRef.name, Invoice.CustomerRef.name, Invoice.ShipAddr.Line1, JSON.stringify(Invoice.Line), Invoice.ShipDate, Invoice.TrackingNum, Invoice.PaymentMethodRef.name, Invoice.TotalAmt, Invoice.Balance, ]); } } catch (f) { log_('INVOICES ERROR: ' + f.toString()); } } The script can be further enhanced to extract details of individual line items like the SKU / Part number, Quantity left, and so. This would however require a separate Rest API call to the following endpoint. https://quickbooks.api.intuit.com/v3/company/companyId/item/' + itemId View the full article

The Google Script will download your Fitbit data via the Fitbit API and insert it into a Google spreadsheet. The first row of the spreadsheet will be a header row containing data element names like steps walked, body fat, calories burned, etc. Subsequent rows will contain data, one day per row. This is a Google Spreadsheet bound script so you need to create a sheet first and put this code inside the Script editor. Fitbit uses metric units (weight, distance) so you may wish to convert them as per your locale. /* Original Fitbit script by [email protected], Further modifications by Mark Leavitt, Christian Stade-Schuldt, Robert Furberg, Amit Agarwal */ // Key of ScriptProperty for Fitbit consumer key. var CONSUMER_KEY_PROPERTY_NAME = 'fitbitConsumerKey'; // Key of ScriptProperty for Fitbit consumer secret. var CONSUMER_SECRET_PROPERTY_NAME = 'fitbitConsumerSecret'; // Default loggable resources (from Fitbit API docs). var LOGGABLES = [ 'activities/steps', 'activities/distance', 'activities/floors', 'activities/elevation', 'activities/calories', 'activities/activityCalories', 'activities/minutesSedentary', 'activities/minutesLightlyActive', 'activities/minutesFairlyActive', 'activities/minutesVeryActive', 'sleep/startTime', 'sleep/timeInBed', 'sleep/minutesAsleep', 'sleep/awakeningsCount', 'sleep/minutesAwake', 'sleep/minutesToFallAsleep', 'sleep/minutesAfterWakeup', 'sleep/efficiency', 'body/weight', 'body/bmi', 'body/fat', ]; // function authorize() makes a call to the Fitbit API to fetch the user profile function authorize() { var oAuthConfig = UrlFetchApp.addOAuthService('fitbit'); oAuthConfig.setAccessTokenUrl('https://api.fitbit.com/oauth/access_token'); oAuthConfig.setRequestTokenUrl('https://api.fitbit.com/oauth/request_token'); oAuthConfig.setAuthorizationUrl('https://api.fitbit.com/oauth/authorize'); oAuthConfig.setConsumerKey(getConsumerKey()); oAuthConfig.setConsumerSecret(getConsumerSecret()); var options = { oAuthServiceName: 'fitbit', oAuthUseToken: 'always', }; // get the profile to force authentication Logger.log('Function authorize() is attempting a fetch...'); try { var result = UrlFetchApp.fetch('https://api.fitbit.com/1/user/-/profile.json', options); var o = Utilities.jsonParse(result.getContentText()); return o.user; } catch (exception) { Logger.log(exception); Browser.msgBox('Error attempting authorization'); return null; } } // function setup accepts and stores the Consumer Key, Consumer Secret, firstDate, and list of Data Elements function setup() { var doc = SpreadsheetApp.getActiveSpreadsheet(); var app = UiApp.createApplication().setTitle('Setup Fitbit Download'); app.setStyleAttribute('padding', '10px'); var consumerKeyLabel = app.createLabel('Fitbit OAuth Consumer Key:*'); var consumerKey = app.createTextBox(); consumerKey.setName('consumerKey'); consumerKey.setWidth('100%'); consumerKey.setText(getConsumerKey()); var consumerSecretLabel = app.createLabel('Fitbit OAuth Consumer Secret:*'); var consumerSecret = app.createTextBox(); consumerSecret.setName('consumerSecret'); consumerSecret.setWidth('100%'); consumerSecret.setText(getConsumerSecret()); var firstDate = app.createTextBox().setId('firstDate').setName('firstDate'); firstDate.setName('firstDate'); firstDate.setWidth('100%'); firstDate.setText(getFirstDate()); // add listbox to select data elements var loggables = app.createListBox(true).setId('loggables').setName('loggables'); loggables.setVisibleItemCount(4); // add all possible elements (in array LOGGABLES) var logIndex = 0; for (var resource in LOGGABLES) { loggables.addItem(LOGGABLES[resource]); // check if this resource is in the getLoggables list if (getLoggables().indexOf(LOGGABLES[resource]) > -1) { // if so, pre-select it loggables.setItemSelected(logIndex, true); } logIndex++; } // create the save handler and button var saveHandler = app.createServerClickHandler('saveSetup'); var saveButton = app.createButton('Save Setup', saveHandler); // put the controls in a grid var listPanel = app.createGrid(6, 3); listPanel.setWidget(1, 0, consumerKeyLabel); listPanel.setWidget(1, 1, consumerKey); listPanel.setWidget(2, 0, consumerSecretLabel); listPanel.setWidget(2, 1, consumerSecret); listPanel.setWidget(3, 0, app.createLabel(' * (obtain these at dev.fitbit.com)')); listPanel.setWidget(4, 0, app.createLabel('Start Date for download (yyyy-mm-dd)')); listPanel.setWidget(4, 1, firstDate); listPanel.setWidget(5, 0, app.createLabel('Data Elements to download:')); listPanel.setWidget(5, 1, loggables); // Ensure that all controls in the grid are handled saveHandler.addCallbackElement(listPanel); // Build a FlowPanel, adding the grid and the save button var dialogPanel = app.createFlowPanel(); dialogPanel.add(listPanel); dialogPanel.add(saveButton); app.add(dialogPanel); doc.show(app); } // function sync() is called to download all desired data from Fitbit API to the spreadsheet function sync() { // if the user has never performed setup, do it now if (!isConfigured()) { setup(); return; } var user = authorize(); var doc = SpreadsheetApp.getActiveSpreadsheet(); doc.setFrozenRows(1); var options = { oAuthServiceName: 'fitbit', oAuthUseToken: 'always', method: 'GET', }; // prepare and format today's date, and a list of desired data elements var dateString = formatToday(); var activities = getLoggables(); // for each data element, fetch a list beginning from the firstDate, ending with today for (var activity in activities) { var currentActivity = activities[activity]; try { var result = UrlFetchApp.fetch( 'https://api.fitbit.com/1/user/-/' + currentActivity + '/date/' + getFirstDate() + '/' + dateString + '.json', options ); } catch (exception) { Logger.log(exception); Browser.msgBox('Error downloading ' + currentActivity); } var o = Utilities.jsonParse(result.getContentText()); // set title var titleCell = doc.getRange('a1'); titleCell.setValue('date'); var cell = doc.getRange('a2'); // fill the spreadsheet with the data var index = 0; for (var i in o) { // set title for this column var title = i.substring(i.lastIndexOf('-') + 1); titleCell.offset(0, 1 + activity * 1.0).setValue(title); var row = o[i]; for (var j in row) { var val = row[j]; cell.offset(index, 0).setValue(val['dateTime']); // set the date index cell.offset(index, 1 + activity * 1.0).setValue(val['value']); // set the value index index index++; } } } } function isConfigured() { return getConsumerKey() != '' && getConsumerSecret() != ''; } function setConsumerKey(key) { ScriptProperties.setProperty(CONSUMER_KEY_PROPERTY_NAME, key); } function getConsumerKey() { var key = ScriptProperties.getProperty(CONSUMER_KEY_PROPERTY_NAME); if (key == null) { key = ''; } return key; } function setLoggables(loggable) { ScriptProperties.setProperty('loggables', loggable); } function getLoggables() { var loggable = ScriptProperties.getProperty('loggables'); if (loggable == null) { loggable = LOGGABLES; } else { loggable = loggable.split(','); } return loggable; } function setFirstDate(firstDate) { ScriptProperties.setProperty('firstDate', firstDate); } function getFirstDate() { var firstDate = ScriptProperties.getProperty('firstDate'); if (firstDate == null) { firstDate = '2012-01-01'; } return firstDate; } function formatToday() { var todayDate = new Date(); return ( todayDate.getFullYear() + '-' + ('00' + (todayDate.getMonth() + 1)).slice(-2) + '-' + ('00' + todayDate.getDate()).slice(-2) ); } function setConsumerSecret(secret) { ScriptProperties.setProperty(CONSUMER_SECRET_PROPERTY_NAME, secret); } function getConsumerSecret() { var secret = ScriptProperties.getProperty(CONSUMER_SECRET_PROPERTY_NAME); if (secret == null) { secret = ''; } return secret; } // function saveSetup saves the setup params from the UI function saveSetup(e) { setConsumerKey(e.parameter.consumerKey); setConsumerSecret(e.parameter.consumerSecret); setLoggables(e.parameter.loggables); setFirstDate(e.parameter.firstDate); var app = UiApp.getActiveApplication(); app.close(); return app; } // function onOpen is called when the spreadsheet is opened; adds the Fitbit menu function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: 'Sync', functionName: 'sync', }, { name: 'Setup', functionName: 'setup', }, { name: 'Authorize', functionName: 'authorize', }, ]; ss.addMenu('Fitbit', menuEntries); } // function onInstall is called when the script is installed (obsolete?) function onInstall() { onOpen(); } View the full article

You have a Google Spreadsheet and you need to programmatically find if a cell with particular value exists in that sheet. Well, Google Scripts can help. You can either search cells in a single column (like A) or the script can search for all cells in the row and return the index of the matching row. function findInColumn(column, data) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var column = sheet.getRange(column + ':' + column); // like A:A var values = column.getValues(); var row = 0; while (values[row] && values[row][0] !== data) { row++; } if (values[row][0] === data) return row + 1; else return -1; } function findInRow(data) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var rows = sheet.getDataRange.getValues(); for (var r = 0; r < rows.length; r++) { if (rows[r].join('#').indexOf(data) !== -1) { return r + 1; } } return -1; } View the full article

The previous snippet on getting Spreadsheets as JSON required you to make a sheet public and also publish as HTML before other apps can pull data as JSON from the sheet. If you would not like to make your Google spreadsheet public, you can use Google Apps Script to create a web app that will expose the sheet data as JSON. /* Source: https://gist.github.com/daichan4649/8877801 */ function doGet(e) { var sheetName = "Sheet 1"; var sheetId = "1234..."; var book = SpreadsheetApp.openById(sheetId); var sheet = book.getSheetByName(sheetName); var json = convertSheet2JsonText(sheet); return ContentService .createTextOutput(JSON.stringify(json)) .setMimeType(ContentService.MimeType.JSON); } function convertSheet2JsonText(sheet) { // first line(title) var colStartIndex = 1; var rowNum = 1; var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); var firstRowValues = firstRange.getValues(); var titleColumns = firstRowValues[0]; // after the second line(data) var lastRow = sheet.getLastRow(); var rowValues = []; for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) { var colStartIndex = 1; var rowNum = 1; var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn()); var values = range.getValues(); rowValues.push(values[0]); } // create json var jsonArray = []; for(var i=0; i var line = rowValues[i]; var json = new Object(); for(var j=0; j json[titleColumns[j]] = line[j]; } jsonArray.push(json); } return jsonArray; } View the full article

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script. View the full article

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script. View the full article

The cloneGoogleSheet() function will copy data (all rows and columns, but no formatting styles) from one Google Spreadsheet to any other Google Spreadsheet under the same Google Drive. You need specify the file IDs of the source and destination Google Spreadsheets as arguments in the formula and also change the source and target sheet names inside the method body. This function can be invoked via a time-based trigger or run it manually from the Apps Script editor. However, if you would like to keep the two spreadsheet in sync with each other always, you an consider using the IMPORTRANGE() Google formula that automatically imports a range of cells from a specified spreadsheet into the currently selected cell / range /sheet. // copy data from Google Sheet A to Google Sheet B // Credit: @chrislkeller function cloneGoogleSheet(ssA, ssB) { // source doc var sss = SpreadsheetApp.openById(ssA); // source sheet var ss = sss.getSheetByName('Source spreadsheet'); // Get full range of data var SRange = ss.getDataRange(); // get A1 notation identifying the range var A1Range = SRange.getA1Notation(); // get the data values in range var SData = SRange.getValues(); // target spreadsheet var tss = SpreadsheetApp.openById(ssB); // target sheet var ts = tss.getSheetByName('Target Spreadsheet'); // Clear the Google Sheet before copy ts.clear({ contentsOnly: true }); // set the target range to the values of the source data ts.getRange(A1Range).setValues(SData); } View the full article

Gravity Forms is an extremely popular Forms plugin for the WordPress. When someone submits a form created with Gravity Forms, the form data is saved inside the MySQL database associated with your WordPress installation. There are paid add-ons through, Zapier for example, that let you do more when someone submits a Form. For instance, you can setup a task in Zapier that will automatically save the Gravity Form data to a specific Google Spreadsheet. Or you can setup a rule where the data is emailed to you as soon as a form is submitted. Zapier offers a visual tool to maps your Gravity Forms to Google Spreadsheets but you can do something similar with Google Apps Script and WordPress hooks without needing to subscribe to Zapier. Let me show you how: From Gravity Forms to Google Spreadsheets First we need to create a web-app with Google Scripts that will receive the Form data from Gravity Forms and either save it to Google Sheets or send it via Gmail. Also see: Get Google Forms Data in Email. Open the Google Spreadsheet where you wish to save the Forms data and create a header row with the column names for all the fields that you wish to save from Gravity Forms. Next go to Tools, Script Editor and paste the following snippet. function doPost(e) { if (!e) return; var sheetID = 'GOOGLE_SPREADSHEET_ID'; // Replace this with the Google Spreadsheet ID var sheetName = 'Form Responses'; // Replace this with the sheet name inside the Spreadsheet var status = {}; // Code based on Martin Hawksey (@mhawksey)'s snippet var lock = LockService.getScriptLock(); lock.waitLock(30000); try { var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName); var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Add the data and time when the Gravity Form was submitted var column, row = [], input = { timestamp: new Date(), }; for (var keys in e.parameter) { input[normalize_(keys)] = e.parameter[keys]; } for (i in headers) { column = normalize_(headers[i]); row.push(input[column] || ''); } if (row.length) { sheet.appendRow(row); status = { result: 'success', message: 'Row added at position ' + sheet.getLastRow(), }; } else { status = { result: 'error', message: 'No data was entered', }; } } catch (e) { status = { result: 'error', message: e.toString(), }; } finally { lock.releaseLock(); } return ContentService.createTextOutput(JSON.stringify(status)).setMimeType(ContentService.MimeType.JSON); } function normalize_(str) { return str.replace(/[^\w]/g, '').toLowerCase(); } Save the Google Script. Go to the Run menu and choose doPost to authorize the Google Scripts. Next choose Publish, Deploy as web app and save your work. Click Save New Version, set access as Anyone, even anonymous and click Deploy. Make a note of the Google Script URL as we will need it in the WordPress snippet. From WordPress to Google Spreadsheets Now we need to write an Action Hook on WordPress side that will send the data to Google Script which will then save the data to Google Spreadsheet. Go your WordPress theme folder and paste this snippet inside your functions.php file. <?php /* Replace XXX with your Gravity Form ID. e.g., gform_after_submission_2 for Form 2 */ add_action('gform_after_submission_XXX', 'add_to_google_spreadsheet', 10, 2); function add_to_google_spreadsheet($entry, $form) { // This is the web app URL of your Google Script create in previous step $post_url = 'https://script.google.com/macros/s/XYZ/exec'; // Put all the form fields (names and values) in this array $body = array('name' => rgar($entry, '1'), 'age' => rgar($entry, '2'), 'sex' => rgar($entry, '3'),); // Send the data to Google Spreadsheet via HTTP POST request $request = new WP_Http(); $response = $request->request($post_url, array('method' => 'POST', 'sslverify' => false, 'body' => $body)); } ?> Save the PHP file and submit a test entry. It should show up in your Google Spreadsheet instantly. View the full article

The Goodreads API helps you query the entire database of books on the Goodreads website. You can find the ratings of books, fetch book reviews, search books by author or even publish your own reviews. This example shows how to connect to the GoodReads website through Google Apps Script, find books by title, parse the XML results as JSON and write the results in a Google Spreadsheet. You can also extend the code to insert the thumbnail of the book image in a spreadsheet cell using the IMAGE function. To get started, go to the Goodreads.com account and create a key. All Rest API methods will require you to register for a developer key. Goodreads will return the response in XML format (see below) and we can use the XML service of Google Apps Script to easily parse this XML response. Here’s the complete example. Remember to replace the API key with your own. function GoodReads() { var search = 'Taj Mahal'; var books = searchBooks_(search); // Write Data to Google Spreadsheet. var sheet = SpreadsheetApp.getActiveSheet(); books.forEach(function (book) { sheet.appendRow([book.title, book.author, book.rating, book.url]); }); } function searchBooks_(query) { var baseUrl = 'https://www.goodreads.com/book/show/', apiUrl = 'https://www.goodreads.com/search/index.xml', apiKey = 'ctrlq.org', searchResults = [], payload = { q: query, key: apiKey }, params = { method: 'GET', payload: payload, muteHttpExceptions: true }; var response = UrlFetchApp.fetch(apiUrl, params); // API Connection Successful if (response.getResponseCode() === 200) { // Parse XML Response var xml = XmlService.parse(response.getContentText()); var results = xml.getRootElement().getChildren('search')[0]; // Save the result in JSON format results .getChild('results') .getChildren() .forEach(function (result) { result.getChildren('best_book').forEach(function (book) { searchResults.push({ title: book.getChild('title').getText(), author: book.getChild('author').getChild('name').getText(), thumbnail: book.getChild('image_url').getText(), rating: result.getChild('average_rating').getText(), url: baseUrl + result.getChild('id').getText() }); }); }); } return searchResults; } View the full article

Dan Thareja has written a Google Script that will let you export all the formulas, cell values and notes from a Google Spreadsheet as a JSON file. You can also pull the Google formulas in another web app or Google Apps script using a GET Request. It should come handy when you are reviewing the formulas of a large sheet. // Credit: github.com/danthareja function doGet(request) { // ID of Google Spreadsheet var json = getNotesAndFormulas(request.parameter.id); return ContentService.createTextOutput(JSON.stringify(cache)).setMimeType(ContentService.MimeType.JSON); } function getNotesAndFormulas(spreadsheetId) { return SpreadsheetApp.openById(spreadsheetId) .getSheets() .reduce( function (cache, sheet) { var sheetData = (cache[sheet.getName()] = {}); var range = sheet.getDataRange(); sheetData.range = range.getA1Notation(); sheetData.notes = range.getNotes(); sheetData.formulas = range.getFormulas(); return cache; }, { spreadsheetId: spreadsheetId } ); } View the full article

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

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

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

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

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

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

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

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

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

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

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

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

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