Categories
Code

How to Get Hidden and Filtered Rows in Google Sheets with Google Script

You can hide entire rows in Google Sheets manually or use filters and hide on or more or rows in the sheet that matches your specified criteria. For instance, if you have a sheet containing orders from different countries, you can set up a country filter to hide all rows where the country is not the United States.

If you have a Google Script that iterates through each row in the Google Sheet for performing actions on the row, like sending emails or merging documents, you can check for the hidden and filtered rows and easily skip them from the workflow.

Also, a row in the Google Sheet can be filtered and hidden at the same time.

function getHiddenAndFilteredRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var d=0; d
Categories
Code

Search Books with Goodreads API and Google Apps Script

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;
  
}
Categories
Code

How to Update Multiple Cell Values in Google Sheets with Apps Script

The SpreadsheetApp service of Google App Script offers the range.setValues() method to update a single cell or a range of cells inside a Google Spreadsheet. You cannot, however, write data to multiple non-consecutive cells in a Spreadsheet using the setValues() method.

The Google Spreadsheet API, available inside Apps Script project through Advanced Google Services, can update multiple cells in one execution. You can write values in single cells, rows, columns or even a 2d matrix of cells.

function updateGoogleSheet(spreadsheetId) {

  /* Written by Amit Agarwal */
  /* Web: ctrlq.org  Email: amit@labnol.org */
  
  var data = [
    { 
      range: "Sheet1!A1",   // Update single cell
      values: [
        ["A1"]
      ]
    },
    {
      range: "Sheet1!B1:B3", // Update a column
      values: [
        ["B1"],["B2"],["B3"]
      ]
    },
    {
      range: "Sheet1!C1:E1", // Update a row
      values: [
        ["C1","D1","E1"]
      ]
    },
    {
      range: "Sheet1!F1:H2", // Update a 2d range
      values: [
        ["F1", "F2"],
        ["H1", "H2"]
      ]
    }];
  
  var resource = {
    valueInputOption: "USER_ENTERED",
    data: data
  };
  
  Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
  
}
Categories
Code

How to Convert Microsoft Excel to Google Spreadsheet Format with Apps Script

If your colleagues have been emailing you Microsoft Excel spreadsheets in xls or xlsx format, here’s a little snippet that will help you convert those Excel sheets into native Google Spreadsheet format using the Advanced Drive API service of Google Apps Script.

function convertExceltoGoogleSpreadsheet(fileName) {
  
  try {
    
    // Written by Amit Agarwal
    // www.ctrlq.org

    fileName = fileName || "microsoft-excel.xlsx";
    
    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: folderId}],
    };
    
    Drive.Files.insert(resource, blob);
    
  } catch (f) {
    Logger.log(f.toString());
  }
  
}

The script finds the existing Excel workbook by name in your Google Drive, gets the blob of the file and creates a new file of Google Sheets mimetype (application/vnd.google-apps.spreadsheet) with the blob.

You do need to enable the Google Drive API under Resources > Advanced Google Services and also enable the Drive API inside the Google Cloud Platform project associated with your Google Apps Script.

The other option, instead of specifying the mimetype, is to set the argument convert to true and it will automatically convert the source file into corresponding native Google Format at the time of insert it into Google drive.

function convertExceltoGoogleSpreadsheet2(fileName) {
  
  try {
    
    fileName = fileName || "microsoft-excel.xlsx";
    
    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName().replace(/.xlsx?/, ""),
      key: fileId
    };
    Drive.Files.insert(resource, blob, {
      convert: true
    });
    
  } catch (f) {
    Logger.log(f.toString());
  }
  
}
Categories
Code

How to Import CSV Files into Google Spreadsheets with Google Apps Script

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:reports@example.com");
  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.

Categories
Code

Convert Excel Files to CSV in Google Drive with Apps Script

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: amit@labnol.org  */
    /* 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);

        }
    }

}
Categories
Code

Copy Google Spreadsheet Data to another Sheet with Apps Script

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);

};
Categories
Code

A Custom Google Spreadsheet Function for Tracking Pageviews

GOOGLEANALYTICS is a custom Google Spreadsheet function that tracks Spreadsheet views with the help of Google Apps Script. It includes the JSDoc @customfunction tag and thus the function will autocomplete when begin typing in a spreadsheet cell.

/**
 * 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: amit@labnol.org 
  */
  
  /* Random ID to prevent browser caching */
  var cache_buster = Math.round(Date.now() / 1000).toString();
  
  /* Client ID to anonymously define the device */
  var client_id = Utilities.getUuid();
  
  // Event Category set to Google Spreadsheets
  var event_category = encodeURIComponent("Google Spreadsheets");
  
  // Event Action set to spreadsheet title
  var event_action = encodeURIComponent(spreadsheet || "Spreadsheet");
  
  // Event Label set to sheet title
  var event_label = encodeURIComponent(sheetname || "Sheet");
  
  var imageURL = [
    "https://ssl.google-analytics.com/collect?v=1&t=event",
    "&tid=" + gaaccount,
    "&cid=" + client_id,
    "&z="   + cache_buster,
    "&ec="  + event_category,
    "&ea="  + event_action,
    "&el="  + event_label
  ].join("");
  
  return imageURL;

}
Categories
Code

Get QuickBooks Data into Google Sheets with Apps Script

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.


// Written by Amit Agarwal
// email: amit@labnol.org
// web: www.ctrlq.org

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
Categories
Code

Convert and Email Google Spreadsheets as PDF Files

You can set up a cron job in Google Drive using Google Apps Script that will send any Google Spreadsheet, or any other document or file in the Drive, to one or more email addresses at a specified time. You can set the time-based trigger to run weekly, daily, every hour and other recurring schedules.

This example shows how to send a Google Spreadsheet to the specified email address automatically as a PDF file daily. The Google Script converts the Google Spreadsheet into a PDF file and sends it to another email address using your own Gmail account. You can further customize the PDF output – like remove gridlines, hide frozen rows, change to landscape mode, etc. by setting the correct export parameters.

Convert & Email Google Sheets

The Email Google Spreadsheet add-on can automatically convert and email spreadsheets in PDF, CSV or Microsoft Excel (xlsx) formats. It can convert the entire spreadsheet or individual sheets.

The premium version of the add-on can automatically email the converted sheets on a recurring schedule (like every hour, daily, weekly or monthly). You can also set up multiple sending schedules and automatically email the sheet to a different set of receipts at different times.

Google Script to Email Google Spreadsheets

If you cannot use the Email Spreadsheet add-on (some Google Apps admins do not allow add-ons), you can write your own Google Script to email the spreadsheet as PDF files.


/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
  // Send the PDF of the spreadsheet to this email address
  const email = Session.getActiveUser().getEmail() || 'amit@labnol.org';

  // Get the currently active spreadsheet URL (link)
  // Or use SpreadsheetApp.openByUrl("<>");
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Subject of email message
  const subject = `PDF generated from spreadsheet ${ss.getName()}`;

  // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
  const body = "Sent with Email Google Sheets";

  // Base URL
  const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());

  /* Specify PDF export parameters
    From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
    */

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=false' + // orientation, false for landscape
    '&fitw=true&source=labnol' + // fit to page width, false for actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid='; // the sheet's Id

  const token = ScriptApp.getOAuthToken();
  const sheets = ss.getSheets();

  // make an empty array to hold your fetched blobs
  const blobs = [];

  for (let i = 0; i < sheets.length; i += 1) {
    // Convert individual worksheets to PDF
    const response = UrlFetchApp.fetch(url + exportOptions + sheets[i].getSheetId(), {
      headers: {
        Authorization: `Bearer ${token}`
      }
    });

    // convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(`${sheets[i].getName()}.pdf`);
  }

  // create new blob that is a zip file containing our blob array
  const zipBlob = Utilities.zip(blobs).setName(`${ss.getName()}.zip`);

  // optional: save the file to the root folder of Google Drive
  DriveApp.createFile(zipBlob);

  // Define the scope
  Logger.log(`Storage Space used: ${DriveApp.getStorageUsed()}`);

  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [zipBlob]
    });
}

The Google Script function will convert each of the worksheets of a Google spreadsheet into a PDF file, compresses all the PDF files in a ZIP file and sends it to a designated email address. You can send to multiple email addresses too – just separate each of them with a comma.

The method currently sends all the sheets of a Spreadsheet in the ZIP file but you can also specify a sheet ID with the &gid parameter to email a particular sheet only. For instance, to send the first sheet, you can set the gid=0 and so on.

Convert Full Google Sheet to a PDF File

The above snippet converts individual sheets into separate PDF files but there’s also a way to convert the entire spreadsheet into a single PDF file. In that case, replace guid= with id=SS_ID (spreadsheet ID) or perform the conversion using DriveApp as shown here.

/* Email Google Spreadsheet as PDF */
function emailGoogleSpreadsheetAsPDF() {
  
  // Send the PDF of the spreadsheet to this email address
  var email = "amit@labnol.org"; 
  
  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Subject of email message
  var subject = "PDF generated from spreadsheet " + ss.getName(); 

  // Email Body can  be HTML too 
  var body = "Sent via Email Spreadsheet";
  
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  
  blob.setName(ss.getName() + ".pdf");
  
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blob]     
    });  
}
Categories
Code

Convert Google Sheet to Excel XLSX Spreadsheet

The previous example shows how to convert Google Sheets to XLS format using the Google Drive API. The response file resource includes exportLinks URLs for the various export formats for Google Spreadsheets. For instance, the Microsoft Excel version of the Google Sheet can be retrieved via this link:

file['exportLinks']['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']

You need to enabled Advanced Drive API in your Google Developers Console project to know the Export URL of a Google Drive file but there’s a way to get the Excel version using the DriveApp service as well.

The getGoogleSpreadsheetAsExcel() method will convert the current Google Spreadsheet to Excel XLSX format and then emails the file as an attachment to the specified user.

function getGoogleSpreadsheetAsExcel(){
  
  try {
    
    var ss = SpreadsheetApp.getActive();
    
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
    
    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
    
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    
    blob.setName(ss.getName() + ".xlsx");
    
    MailApp.sendEmail("amit@labnol.org", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});
    
  } catch (f) {
    Logger.log(f.toString());
  }
}

Categories
Code

How to Write JSON to a Google Spreadsheet

The WriteJSONtoSheet method take a JSON object and appends it as a new row in a Google Spreadsheet. It takes the keys of the JSON object, matches them with the header row of the spreadsheet and arranges the columns accordingly. If there’s no column corresponding to a key, it is automatically added. This is handy for logging operations and data objects in a Google Sheet for easy analysis.


// Written by Amit Agarwal www.ctrlq.org

function writeJSONtoSheet(json) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var keys = Object.keys(json).sort();
  var last = sheet.getLastColumn();
  var header = sheet.getRange(1, 1, 1, last).getValues()[0];
  var newCols = [];

  for (var k = 0; k < keys.length; k++) {
    if (header.indexOf(keys[k]) === -1) {
      newCols.push(keys[k]);
    }
  }

  if (newCols.length > 0) {
    sheet.insertColumnsAfter(last, newCols.length);
    sheet.getRange(1, last + 1, 1, newCols.length).setValues([newCols]);
    header = header.concat(newCols);
  }

  var row = [];

  for (var h = 0; h < header.length; h++) {
    row.push(header[h] in json ? json[header[h]] : "");
  }

  sheet.appendRow(row);

}
Categories
Code

Convert Word, Excel and PowerPoint files to Google Docs with Google Script

You can store your Microsoft Office files (Word Documents, PowerPoint Presentations and Excel Spreadsheets) in Google Drive in their native format but then it takes up storage space, the files cannot be edited in the cloud and you’ll not be able to embed the files on other web page.

For instance, you can embed a Google Sheet, or a portion of it, in your web page but not if the file is in the xls or xlsx format. A simple solution therefore would be to convert the Office documents into the corresponding Google Document formats and this can be easily done with Google Apps Script.

This Google Script will convert Office files to the Google format using the Advanced Drive API. It then renames the converted document to the original filename but without the extension. You will have to enable the Advance Drive API for your Apps Script project through the Google Developers Console.


// Written by Amit Agarwal www.ctrlq.org
// Email: amit@labnol.org

function convertDocuments() {
  
  // Convert xlsx file to Google Spreadsheet
  convertToGoogleDocs_("Excel File.xlsx")

  // Convert .doc/.docx files to Google Document
  convertToGoogleDocs_("Microsoft Word Document.doc")

  // Convert pptx to Google Slides
  convertToGoogleDocs_("PowerPoint Presentation.pptx")

}

// By Google Docs, we mean the native Google Docs format
function convertToGoogleDocs_(fileName) {
  
  var officeFile = DriveApp.getFilesByName(fileName).next();
  
  // Use the Advanced Drive API to upload the Excel file to Drive
  // convert = true will convert the file to the corresponding Google Docs format
  
  var uploadFile = JSON.parse(UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true", 
    {
      method: "POST",
      contentType: officeFile.getMimeType(),
      payload: officeFile.getBlob().getBytes(),
      headers: {
        "Authorization" : "Bearer " + ScriptApp.getOAuthToken()
      },
      muteHttpExceptions: true
    }
  ).getContentText());
  
  // Remove the file extension from the original file name
  var googleFileName = officeFile.substr(0, officeFile.lastIndexOf("."));
  
  // Update the name of the Google Sheet created from the Excel sheet
  DriveApp.getFileById(uploadFile.id).setName(googleFileName);
  
  Logger.log(uploadFile.alternateLink);  
}

The files are created the root folder of Google Drive.

Categories
Code

Save Paypal Email Receipts in Google Spreadsheet

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(.*?)<\/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;
  
}
Categories
Code

Build a Charts Dashboard with Google Sheets and HTML Service

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.

google-charts-dashboard


// 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






  




  

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.

Categories
Code

Parse Gmail Messages to Extract Data

A mailbox has 1000s of email messages sent though legacy contact forms that contain data like the name, email and address of the senders. The business owner would like to parse these email messages, extract the relevant bits and save them to a Google Spreadsheet.

The script can be run in batches of 100 thread, to avoid exceeding the time limit, and the parsing rules can be written in Regular Expressions. Snippet by @Ferrari.

The code can extended to parse emails and extract other structured data from the message body including events information, order details, travel itineraries, shipping & tracking information, customer records and more.

/* Based on https://gist.github.com/Ferrari/9678772 */

function parseEmailMessages(start) {

  start = start || 0;

  var threads = GmailApp.getInboxThreads(start, 100);
  var sheet = SpreadsheetApp.getActiveSheet();

  for (var i = 0; i < threads.length; i++) {

    // Get the first email message of a threads
    var tmp,
      message = threads[i].getMessages()[0],
      subject = message.getSubject(),
      content = message.getPlainBody();

    // Get the plain text body of the email message
    // You may also use getRawContent() for parsing HTML

    // Implement Parsing rules using regular expressions
    if (content) {

      tmp = content.match(/Name:\s*([A-Za-z0-9\s]+)(\r?\n)/);
      var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No username';

      tmp = content.match(/Email:\s*([A-Za-z0-9@.]+)/);
      var email = (tmp && tmp[1]) ? tmp[1].trim() : 'No email';

      tmp = content.match(/Comments:\s*([\s\S]+)/);
      var comment = (tmp && tmp[1]) ? tmp[1] : 'No comment';

      sheet.appendRow([username, email, subject, comment]);

    } // End if

  } // End for loop
}

Categories
Code

Export Formulas and Notes from a Google Spreadsheet

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 });
}
Categories
Code

Save Gmail Messages to a Google Spreadsheet

The Google Script from @oshliaer will save the body of email messages from Gmail to the currently active worksheet inside 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: https://gist.github.com/oshliaer/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);
    }
}

Categories
Code

Send Gravity Forms Data to Google Spreadsheet or Email

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.

 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.

Categories
Code

Get Google Spreadsheets Data as JSON in your Website

You can retrieve the content of any public Google Spreadsheet in your web app using JSON feeds. The sharing permissions of the Google Spreadsheet should be either “Public” or set to “Anyone with link can view” for the app to fetch cells from the Google Spreadsheet without authentication.

You will also need to publish the sheet (File -> Publish to the web -> Publish) for the data to be accessible from your website or REST powered web app.

The JSON and XML feeds for any Google Spreadsheet is available at:

JSON Format:
https://spreadsheets.google.com/feeds/list/SPREADSHEET/od6/public/basic?alt=json

XML Format:
https://spreadsheets.google.com/feeds/list/SPREADSHEET/od6/public/values

Here’s a sample jQuery based example that pulls data from a public spreadsheet in Google Drive as JSON and prints as HTML. This can be clubbed with IFTTT or Zapier for more useful integrations.


Categories
Code

Reorder Worksheets in Google Spreadsheets by Name

You have a Google Spreadsheet with multiple worksheets and, to making organization easier, you would like to reorder or sort the various sheets inside the spreadsheet by name.

Google Apps Script to the rescue. Paste the snippet inside the Google Sheet’s script editor and run the sortGoogleSheets method from the menu.


/* Credit: https://gist.github.com/chipoglesby/26fa70a35f0b420ffc23 */

function sortGoogleSheets() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Store all the worksheets in this array
  var sheetNameArray = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort();

  // Reorder the sheets.
  for( var j = 0; j < sheets.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j + 1);
  }
}
Categories
Code

Save Google Sheet as JSON

The ExportSheetAsJSON() method will save the currently active sheet of a Google Spreadsheet as a JSON file and saves it to your Google Drive. The header (first row) are used as attributes.


/* From https://gist.github.com/IronistM/8be09ebd4c5a4a58c63b */

function exportSheetAsJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numCols = rows.getNumColumns();
  var values = rows.getValues();
  
  var output = "";
  output += "{\""+sheet.getName()+"\" : {\n";
  var header = values[0];
  for (var i = 1; i < numRows; i++) {
    if (i > 1) output += " , \n";
    var row = values[i];
    output += "\""+row[0]+"\" : {";
    for (var a = 1;a 1) output += " , ";
         output += "\""+header[a]+"\" : \""+row[a]+"\"";
    }
    output += "}";
    //Logger.log(row);
  }
  output += "\n}}";
  Logger.log(output);
  
  DriveApp.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);
  
};
Categories
Code

Convert Google Documents and Spreadsheets with Apps Script

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.


// Credit Stéphane Giron
// Save the Google Document as HTML

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();
}

// Credit: Eric Koleda
// Export Google Spreadsheet as Microsoft Excel format

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();
}

Categories
Code

Publish Google Spreadsheets as JSON with Apps Script

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

		
Categories
Code

Find Matching Rows in Google Spreadsheets

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