What would you do if your job contains translating excel files into each other?

Like everyone, I had also a part of excel reporting in my job. Every beginning of the week, we’re getting an automated mail from reporting system with excel attachment. So I started my weekend project to automate these excel files.

There are 4 steps, how I did my automation project. Before automation steps, take a look to incoming data below.

An example of incoming data

1. Prepare Data Collecting (Query) Sheet for Excel

If I can find information in any excel, this means Excel formulas also can get the same information with formulas like vlookup, hlookup, indirect etc. So first step is to collect data from a central point as a sheet.

Alt text

Query sheet is consisted from vlookup(), hlookup(), indirect(), concatenate() formulas to address the the right cells and sum(), sumifs(), averageifs() etc. formulas to calculate desired outcome from raw data.

I call this sheet as query sheet, which makes easy to get recurring data by just inserting a siingle, pre-defined excel sheet. This sheet needs to be separated as another file to use for recurring excel files. (I will use this sheet for each incoming data again.)

With right click on the query page, I create a new excel file. In this file, cells will look empty. (Because iferror() formula is used) If excel seems like “N/A”, there is no problem. It can be kept as-are.

Then I import this “query.xlsx” file to Google Sheets. After import, it is needed to keep spreadsheet id of “query.gsheet” file. (After import, it is free to change the directory of gsheet file into Google drive.)

2. Process Excel File with Google Apps Script

The backbone part of this project is Google Apps Script which handles excel files, converts to the proper format, storages data then report our data.

Google Apps is a good environment to run Google-based products like Gmail, Google Drive, Google Documents, Bigquery and more. Steps for Data Processing Script on Google Apps

How does script work?

Google Apps script is based on JavaScript, it’s called Google Script.

Our need to process data consists of different steps, visible on the image at left.

2.1. Getting Excel File from Get Method

Google Apps script, supports to handle post and get requests (2) and this ability allow us to use Google Script as a mini-backend. By this way, we trigger the script like an API call.

The main script starts with doGet() method, which handles the link parameter at the end of the request URL. For example, https://gs.com/script_id/exec?file=filelink.com/file.xlsx link will be handled by this link and the related code block below.

function doGet(e) {
    // e.parameter["file"] //returns ?file=filelink.com/file.xlsx
    file = UrlFetchApp.fetch(e.parameter["file"]).getBlob();
}

2.2. Putting Excel File to Drive Folder

After getting the file, the script inserts file into the specified (3) Drive folder. But we need to enable file convert (bold below) to get data through Google Spreadsheets APIs. (4)

var fileInfo = {
    title: filename,
    mimeType: "MICROSOFT_EXCEL",
    "parents": [{'id': DataFolderId}],
};file = Drive.Files.insert(fileInfo, excelfile, {convert: true});//file.id //

After setting file, file id can be get by .id method

Up to now, I just handled the scenario to put excel files into the Drive as a new Google Spreadsheet.

2.3. Merge with Query Sheet

I add the query page (query.gsheet, seem as source) to inserted data (seem as destination) with code block below.

var source = SpreadsheetApp.openById(idOfQuerySheet);var sheet = source.getSheets()[0];var destination = SpreadsheetApp.openById(insertedDataID);sheet.copyTo(destination);

2.4. Add Extracted Data to Master Data Sheet

Then I should insert extracted data to master data sheet which will contain the extracted data from all of the weekly shared data.

// Defining Master and Weekly Data Files
var master = SpreadsheetApp.openById(mSS).getSheetByName(mSheet);
var data_sheet = SpreadsheetApp.openById(file.id);

// Get last page, because query page is added to the end
var sheetNumber = spreadsheet.getNumSheets();
var lastSheet = data_sheet.getSheets()[sheetNumber-1];

// Getting Data from cell range
var data = lastSheet.getRange("G3:X3").getValues()[0];// Add to Master Sheet
master.appendRow(data);

You can find my script details from source code (here) on my datAutomation repository here.

2.5. Publish Script as Web Service

Google Script panel allow distributing script as a web service. From publish -> Deploy as Web App pane, it generates a complicated request URL.

Alt text

Before deployment, it’s important to check the execute and access preferences. Especially “execute the app as” is the most important preference. Apps Script needs to authenticate for each of API’s (Drive API, Email App API, Spreadsheet App API) used in the script. All of these APIs are authenticated for my account. So I also set “execute as” preference as me. (This may cause a potential leak, please take your step carefully.)

IFTTT Applet which sends mail attachments to Apps Script

IFTTT Applet which sends mail attachments to Apps Script

3. Auto Forward Attachment with IFTTT

My Apps Script will need to get the attachment in an automatical way. I use IFTTT to send mail which contains data attachment.

At left, my IFTTT recipe works as

If This: A mail from (my e-mail address) with the tag in the subject.

Then That: Make a Web Request with Attachment URL

I use the request URL, generated at distribution screens, above. My request URL takes attachment from URL parameter as (?file=file_address). This file address has taken as AttachmentTemporaryUrl supplied from IFTTT.

4. Prepare a Summary Mail then Auto Send

4.1. Mail Preparation

If excel labour is repeatable in the same way, its report also will be repeatable in the same way.

So, let’s create a comment sheet, which summarizes data like query sheet at step 1.

A report is basically the comparison of new data with previous ones. According to this approach, my automated report will compare the last two data log.

At the sample comment sheet below, there is a mini table which gets last and previous data. It uses Indirect formula to address cells, which contains data.

At the below, there is some formula to determine what to write like;

=IF(G6/H6 > 1;
    CONCATENATE(
        ROUND((G6-H6) / H6 * 100; 0); " percent increase");
    CONCATENATE(
        ROUND((H6-G6) / H6 * 100; 0); " percent decrease"))

This formula detects the change and defines the verbal statement.

Another cell is concatenating all of the verbal statement like above. Please check the sheet below. Sample Comment Sheet Sample data, Getting last two data entries, prepare a message with conditions, concatenate a mail

Apps Script also supports MailApp which allow sending emails behalf of the user.

var SS = SpreadsheetApp.openById(SampleCommentSheet);var sheet = SS.getSheetByName(Comment);var message = sheet.getRange("J3").getValue();MailApp.sendEmail(address, subject, message);

Let’s Test

Three, two, one, go…

I forward(5) this mail below to IFTTT mail address.

A mail with attachment to my automation project A mail with attachment to my automation project

After 6seconds, here is my summary mail, below.

Report summary mail from automation project Report summary mail from automation project

Data also added to the master spreadsheet. =)

Notes

1.All of the data is generated randomly and all of the stylings are removed in this article.

2.A very helpful guide: http://googleappscripting.com/doget-dopost-tutorial-examples/

3.Specific folder and file ids can be learnt from the address bar, id section is highlighted for a folder example.

url

4.All of the API’s requires separate permissions, Apps Script asks for permissions in order.

5.I prefer mail forwarding instead of automatically forwarding emails to debug my script, but as I mentioned at IFTTT section, it’s also possible to define an auto-forwarding rule, which sends data attachment to IFTTT’s address.

Also, please check that IFTTT and Google Apps Script is convenient to store or transmit your data.