Skip to main content

Posts

How to embed a google app script onto google sites

I recently saw some online discussion asking about how to manage the issue of the URL changing for google app script code deployments. There is a really simple way of resolving this using google sites. I've described how to do it below. First, create the google app script app, as you would any app, deploy the app as a Web app and you'll soon have the lengthy, difficult to remember and generally not that nice URL in the address bar. This little app, records your name and displays it once submitted. Perfect for this example. Then, head on over to google sites via sites.new   Click the 'Embed' option on any page Click the 'Embed' option on any page In the 'Embed from the web' pop up, paste in the URL for the app script web app and click 'insert. You'll then see your web app displayed on the page Click the 'Embed' option on any page Once it displays, resize it as required. Then click 'Publish' Enter a suitable web site name if you...
Post a Comment
Read more
Recent posts

How to handle changing column names in Power Query

When working with spreadsheets and tables within spreadsheets it can be common for the column names to change. This can be deliberate or accidental…. Fortunately there is a transformation in the Power Query interface that can deal with column header issues easily. Note - if the order of the columns change this wont work! In this example I have a table with 3 columns as shown here The associated query includes a transformation to create a new column with 10% of the price, called Fee. As you can see the column titled Price is included in the formula to create the new 'Fee' column. If for any reason this column changes then this formula will fail and present an error. To demonstrate this I'll change the column title to 'Price ($)' and refresh the query. As expected, the Fee column returns an error as the Price column has changed. The error details the following issue The first line of the error clearly states the Price of the record wasn’t found. How to fix this issue ...
Post a Comment
Read more

Power Query - Inserting blank rows at specific points

A recent addition to the 'split by delimiter' options allows a column to be split to rows as opposed to columns. This has a number of benefits but it can also be used to insert blank rows into a dataset. Update - to do this with multiple columns there are some additional steps, I've covered these at the bottom of this post. For example, if we had a list of colours and we wanted to add 2 blank rows between each change of colour Can be transformed to become… This is how its done. First, load the data to Power Query. For this example I've used the 'enter data' function to directly paste it into the data model. You can use the 'get and transform function in Excel to load a CSV or Excel file as required. Then, add an index from 0 and change the column name to 'Primary' Next we'll add another index from 1 and change the column name to 'Secondary' Once these are added, we'll use the 'Merge' function to merge the table with itself usi...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more