Getting Productive With Google Apps

We’ve just released a new title called Getting Productive With Google Apps, which introduces Google Apps to small and medium sized businesses as a method of improving the technology base, simplifying the infrastructure and cutting costs. The book should be listed on Amazon.com shortly but please click here if you are interested in purchasing a copy ($19.95).

Returning a weekday name from a date in Excel

Sometimes the simple tips are the best: while the WEEKDAY function returns a value from 0-6 (or 1-7) indicating the number of a day in the week, translating this to a human-readable name can be frustrating.

There is a remarkably simple way to do this though:

=CHOOSE(WEEKDAY(cell reference),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)

Using the CHOOSE function, the above formula will simply return the human-readable day of the week.

Using GMail for Corporate Email – Case Study

415 Systems has typically focused on MIcrosoft Office-oriented solutions and Sharepoint deployments, so several recent trends have been very noticeable:

• Firms are looking to cut licensing costs for what they consider to be “standard applications”, like email, word processing and spreadsheet programs.
• More people are telecommuting or requiring remote access to applications while traveling.
• Companies wish to comply with email retention policies and security practices without large investments in infrastructure or headcount.

The traditional Windows/Office/Exchange model has also been under fire for three other reasons:

• The unpopularity of Vista among business users and apparent abandoning of XP by Microsoft.
• The learning curve for Office 2007 and usability issues around enhanced security and the Fluent Ribbon.
• The difficulty of administering Exchange, and complexities of providing remote access.

Our Case Study Client.

Our client provides compliance services to independent financial advisers, and has three separate offices nationwide with 12 employees in total. They are legally required to permanently store emails, and the constant emailing back-and-forth of Excel documents and PDFs creates confusion and causes errors. They have one part-time IT support resource and one server located on the West Coast, which makes access for the other two offices slow and unreliable. The nature of their business is very workflow-based and close co-ordination and scheduling is key building their scale.

Why the GMail solution?

We had looked at GMail a year ago for another project and decided it was not ready for the corporate environment. But like many software products that live in ‘the cloud’, their corporate offering has changed substantially since then.

A number of key changes made GMail viable:

• SSL/HTTPS access, ensuring not only secure sign-on but the encryption of transmissions over wireless networks.
• 99.9% uptime and phone support.
• Email security and archiving by Postini.

While there is a free corporate GMail, it doesn’t provide the above features, and the $50 per user annual cost represents a substantial saving over a comparable Exchange installation. Additionally, compared to other online email solutions, GMail is the only service that is as fast a desktop application and functions seamlessly with PDAs and smart phones.

Document Collaboration.

Although not the primary goal of the project, the Google Docs collaboration platform has quickly shown itself to be superior to the MIcrosoft alternative. In the Microsoft model (Office Live), documents are downloaded to your desktop client, modified and then saved back to the server. The problem is that users often forget to save the latest version back to the server, or start reverting to the habit of sending email with attachments of the files, and versioning becomes a problem.

In the Google Docs world, there is no concept of the location of the file, and multiple users can open the document or spreadsheet simulataneously and update in real time. Docs remembers every version of the file, making it trivial to see changes at previous points in time. Although the Docs platform is not as fully-featured as the Microsoft Office equivalent, our experience suggests that users rarely use the more complex features, and migrate to the Google Docs environment surprisingly easily.

Conclusion.

The success of this project has given us confidence to suggest the Google platform as a robust solution to the email and Office needs of SMEs. In this case, our client’s cost of $600 per year was a fraction of the five-figure cost quoted for the traditional Exchange implementation. In summary, here’s how the two solutions compare:

	Microsoft Office	Gmail/Google Docs
Ability to work offline	Yes	Not for Gmail but available for Docs
Local server and infrastructure	Yes	No
Requires local IT support	Yes	No
Functionality provided	Advanced to expert	Basic to intermediate
Licensing model	Per machine	Per user
Email archiving	Third party available	Yes
User provisioning/admin	Complex	Easy
Shared calendars	Yes	Yes
Learning curve	Complex	Easy
Operating System	Windows	Any
Implemention period	Medium	Short
Upgrades	Costly/time consuming	Transparent/included in user fee

Create a portfolio spreadsheet containing market data

Using Google Spreadsheets, it’s trivial to create a spreadsheet with updating (but delayed) market data – like this. The formula for each cell follows the format =GoogleFinance(symbol,attribute), so to retrieve the quote for Microsoft, enter =GoogleFinance(“MSFT”,”quote”). Although the quotes are delayed by 20 minutes, you can then use the Google Gadget graphing capabilities to see the composition of your portfolio. And, of course, you can share with your friends in real time, as always with Google Spreadsheets.

For detailed documentation about different market data attributes, as well as information on accessing historical data, click here.

Integrating Google Docs, Yahoo Pipes and iGoogle

One of the rapidly-emerging Web 2.0 by-products is the glue that enables a data consumer to absorb, transform and reproduce data, which allows us to treat large web data repositories as if they were files on a local machine. The result is that you can mash up unthinkably large and complex amounts of data with no code. What would previously have taken Herculean efforts and cutting-edge development to produce, now merely takes a few minutes and a little imagination. Even the Microsoft approach that leans toward writing snippets of Javascript seems to have taken the road of complexity compared with what’s been designed in the Google and Yahoo campuses.

“Show me a picture of every legally-recognized country on an interactive map.”

So here’s an interesting project – I want to show a photo of each country recognized by the ISO organization on my personalized iGoogle page, like this:

The way this works is complicated, mainly to demonstrate the way feeds can be make to work together:

• Import the table of countries from the ISO website to a Google Spreadsheet (http://www.iso.org/iso/country_codes/iso_3166_code_lists/english_country_names_and_code_elements.htm).
• Publish the Google spreadsheet as a web csv file (http://spreadsheets.google.com/pub?key=p7BlQgZBeja_e7jj7TZCjKw&output=csv&gid=2)
• Grab a picture for each country from Flickr.
• Put each picture on a map identifying each country’s location.

Step 1: The import

In a new Google Spreadsheet, enter this formula in cell A1: =importhtml(“http://www.iso.org/iso/country_codes/iso_3166_code_lists/english_country_names_and_code_elements.htm”,”table”,1). The importhtml function is a very useful and little-known function that tells Google the webscrape the first parameter, look for a table, and import the first table it finds. There’s more documentation on this function here.

Step 2: Publish the spreadsheet

Simply select Share –> Publish as a Web Page, and under “More publishing options”, select CSV. You could also set up RSS subscriptions instead.

Step 3: Getting images from Flickr

Sign up for a Yahoo account if you don’t have one and go to Yahoo Pipes. Create a New Pipe, and add a “Fetch CSV” module. For the URL, use the one Google provided when you published the CSV, and tell it how to interprest the columns (see the screenshot below for details). Then add a Loop module, and a Flickr module within that – for each item in the loop, have the Flickr module search for the “Country” in its image database. Finally, emit all output to the results. This is easier to understand if you click the pipe here and view the source:

The result is a Yahoo Map that should pushpins with pictures for each country. Of course, this could be refined much further, and simplified to avoid the initial HTML scrape to Google, but I think it’s a fascinating way to see how pumping data from one service to another opens a new realm of mashups. The piping concept isn’t new for any Unix fans out there, since the power of Unix can be largely attributed to this, but expanding this to the web takes the idea to a whole new level.

Finally, if you want to add as a Gadget to your iGoogle page, simply click ‘Add stuff’ and supply the URL for the pipe, and you get this:

Some other uses…

 

• “Create a Bloomberg News source for my iGoogle page”: take a look at this pipe to see how simple it is to create a pipe that listens to the Google News RSS feed and extracts only the Bloomberg news articles. Add the pipe to iGoogle is the same as above – just provide the URL.
• Similarly, filter news sources for news about your company or industry, and embed this dynamic list of headlines on your website.
• Display all US area codes on a map.
• Show all your business retail locations on a Google Map, and embed on your website for customers to see where you are.

Using the Google Maps gadget with Google Docs

If you’ve ever had a list of geographical data in a spreadsheet and wanted to show this visually on Google Maps, this post is for you. Traditionally, getting this to work from a Microsoft Excel spreadsheet involves some complex trickery and third-party plug-ins, but now Google Gadgets makes this very easy – and free.

Build the dataset and creating the map

If you haven’t signed up for a Google account, do this first to get access to Google Docs. Once there, click on New –> Spreadsheet to create a blank workbook.

In the first column, add the addresses you want to track, and in the second column, add the tracking data you would like displayed on the map (eg. person’s name, salesman’s revenue, etc.) Once complete, click on Insert –> Gadget –> Maps and select the Google Map. This will then appear on your workbook:

You need to define the range of data (eg. A1:B100 would take completed entries in the first 100 rows). And you can optionally add a title, and change some of the interaction options of the map. You can view the live page by signing into Google and clicking here.

Embedding the results in your webpage

Click the options arrow in the top right-hand corner of the map, and select ‘Publish’. This will show you the script needed to embed this map into any HTML page. The resulting embedded map will be linked to your underlying data in the spreadsheet.

Other Google Gadgets

Google has a range of existing gadgets that you can attach to your Google Docs, but they have also exposed the API to enable you to write your own. Although some of the gadgets are designed purely for fun, there is a growing range of gadgets that extends the basic spreadsheet functionality to include functions previously only available in Microsoft Excel. For example, pivot tables can now be achieved through a gadget and unlike their Excel counterpart, they update in realtime. You can access all of this functionality from the Insert –> Gadget options.

We expecting the functionality of custom gadgets to grow exponentially in the next few months, as Google Docs becomes a serious contender to replace Microsoft Excel.

Send a survey to your customers and get results realtime

If you’ve ever wanted to send a survey to your customers (or any type of form to a group), traditionally this would require you to design an email, and then parse each customer response into a spreadsheet. Or you could find a web programmer in your company to throw together an ASP page (presuming you don’t have complex firewalls and infrastructure) or pay for a service such as Survey Monkey.

But with the new features of Google Spreadsheets, you can create a form and collate its results in seconds, and today’s post will show you how.

Designing the form.

Create an account at http://docs.google.com/ if you don’t already have one, and click on New –> Form. The form designer opens, which is a simple interface for putting together your questions. Here you can specify the type of question and response (text, multiple choice, etc.) and if responses are compulsory. Once you have finished, it will look something like this:

At the bottom of this window, you can see the form’s URL that you can send to your survey group. In this example it’s https://spreadsheets.google.com/viewform?key=p7BlQgZBeja8TAfFx1ObCZg. Note this URL is also secure, so responses are encrypted between the recipient and the server. 

In the top right, you will see a button called ‘More Actions’, which provides the code for embedding the survey in an iFrame (which you would use if you wanted to show the survey as part of another webpage), and also let’s you change the confirmation text that your user will see when they have completed the survey:

Gathering the feedback

Back in your Google Docs dashboard, you’ll see your newly-created form:

Click this item to open its underlying spreadsheet, which will update in realtime as respondents complete the survey:

From here, click the ‘Share’ button in the upper right-hand side, and you can choose which people can view and edit the results. I’ve published the sheet with rights for everyone with a Google account to view here. You can publish as a webpage (https://spreadsheets.google.com/pub?key=p7BlQgZBeja8TAfFx1ObCZg) which opens the page to universal access (but does not automatically update).

Furthermore, clicking the ‘Analysis’ button provides realtime charting of the responses:

Benefits and Limitations

Pros:

• This is probably the fastest and simplest way to send forms out to groups, and could be used to gather information from your internal groups, external customers, or even just as a form of Evite for a gathering. 
• The Microsoft Office alternative would be through Infopath or the Email to Spreadsheet/Database option, which is a little similar but more complicated, and relies on more licensed software.
• There are no limitations on the number of forms or questions and it’s free.
• Integration with the spreadsheet and analytics makes data-gathering completely automated.
• There are no problems posed by corporate firewalls or security (unless your company policies prohibit its use).
• Everything happens over SSL and data is stored in the cloud.

Cons:

• You cannot remove the ‘Powered by Google Docs’ or Google Terms which appear on the survey.
• You cannot parse or validate the entries provided.
• You cannot collect IP addresses or leave cookies to prevent multiple responses from one recipient.

Next, let’s look at some of the Google gadgets that can be used to bring these spreadsheets to life.

Access 2007 runtime distribution tips

Although Access 2007 Runtime is a completely free download from Microsoft, and their developer tools provide MSI packaging for the simplest of installations, there are a few other tweaks that are useful to know for more complex installations. We’re using InstallAware instead of the Developer Tools, but these tips mostly apply to any installation packager.

Trusted Locations

Office 2007 doesn’t allow users to open third party MDB/ACCDB files without prompting the user about a potential security risk. Obviously, this doesn’t look good if you are distributing software, so make sure your installation package adds the following key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location0\

… with the following subkeys:

- Path = “your install path” (which is $TARGETDIR$ for InstallAware)
- Date = any arbitrary value
- Description = “”
- AllowSubfolders = 0 or 1 (if you have additional subfolders with databases, set this to 1).

Controlling the 2007 color scheme

There are three global color schemes for the Office suite, introduced in the 2007 version. These are set in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common

… where there is an entry called Theme, and the the value corresponds to the color (1= blue, 2 = silver and 3= black). Setting this will change the scheme for all Office applications. There’s currently no way to be notified if the user opts to change the scheme after the installation.

Downloading the 50MB runtime package

Including the runtime in your install package will result in a very large MSI, and you may want to download this on the fly to avoid the extra bandwidth usage on your own deployment servers. We have a simple .NET program that checks to see if Access 2007 (or runtime) is installed and, if not, downloads this from the Microsoft website and runs the install. This program is called by the installation MSI prior to installing our software, and helps keep our packages small. Please let us know if you need more details of how this works.

Handling corruption in Access databases

Corruption of mdb and accdb files continues to be one of the biggest problems in Access development. While nothing is as good as making regular (daily) backups of your files, there are some additional steps you can take to recover if disaster strikes.

Avoiding corruption in the first place

• Always disable ‘Track Name AutoCorrect Info’ in Access Options for your database. This feature has been found to cause endless problems and the majority of MVPs advise users to switch it off. It’s on by default and turns itself on for every new database you create.
• Store your data in a separate MDB file or – better – in Microsoft’s SQL Server Express database, which is free and links seamlessly to Access, and create table links in your front end database. Storing both code and data in the same database file has been known to cause corruption.
• Don’t store OLE objects or pictures in your database. Apart from the ‘database bloat’ that occurs, this can also cause file corruption. Instead, use links to the data that are stored elsewhere.
• Compact and repair regularly – in Access 2007, this option can be set to automatically run when you close the database.
• In a multi-user environment on a network, we strongly advise you to move the data to SQL Server Express and have users run their own copy of the frontend MDB/ACCDB. Shared mdbs are prone to corruption if users are all sharing the same file, and if the data is in that file.
• Don’t edit code in break mode – Access will frequently ignore the changes you make and this results in lost code. It can also result in confusion where you are seeing one version of the code but Access is actually using its compiled in-memory copy.
• Ensure you set all objects to Nothing when you are finished with them – this is especially true of recordsets, which cause memory leaks if not closed (rs.close) and then set to Nothing.

Recovering from corruption

 

• First, make a backup of the corrupted file and ensure you only attempt recovery on the backup.
• Load your database using the /decompile switch (eg. ”C:\Program Files\Microsoft office\office\msaccess.exe” /decompile “c:\myDatabase.mdb”), compact again and try opening once more. This has been known to fix about 75% of all corruption problems.
• Create a new database and import all the objects from the old database. If Access crashes while doing this, import one object at a time until you find the object that’s causing the crash. Frequently there will be one object that you will not be able to recover that is the cause of the problem.
• Use the Jet Comp utility from Microsoft.
• If all else fails, trying a database recovery service. Usually these are inexpensive, especially considering the cost of starting from scratch, and they can dismantle the corrupted databse and return a working one.

If you’ve forgotten your database password, there are tools online to help recovery these. This has become significantly more difficult in Access 2007 since the password is used as an encryption key and brute-force attacks can take a very, very long time. If none of this helps, feel free to email us and we’ll try to help.

Auditing and viewing historical table contents

Audit every change made to a table and see the table’s exact state a historical point in time.