Generate word document from google sheet

Generate a document out of google sheet is one of the tasks businesses wants when maintaining their data in spread sheets. It can be from PO, Bill of Lading, Invoice or what ever. Google makes it really easy for us to do this through App scripts. All  you need to have is a bit of java script knowledge.

Google does this magic by combining google api's and javascript run on it server (not everything in java script works here).

Lets assume we have a simple google sheet as below.

Lets say we want to generate a document as below.

There are 2 ways to do this.

  • Add a App script bound to the sheet (applies only to this script).

– Pros: We dont need to leave the sheet we are working on.

Can create a menu link to trigger script easily.

Write script by taking current selected range into consideration.

Business people loves it as they dont need to modify anything in the script.

– Cons: The script is applicable only to that sheet. There are ways to make it                                       available to other sheets also.

  • Create a independent App script and send sheet_id, rows to generate document as a variables.

– Pros:  It can be used for any sheet (by supplying the sheet id and row range info).

– Cons: You should know where the App script lives,  enter the the sheets id and                    rows range. It might be painful for the business people to enter all this low level                  details.

I will explain the first approach here(my favorite).

Create a document template:

Create a order summary template like this.

order summary template

Create a App script:

On the sheet menu go to `Tools` –> `Script Editor`

script editor

Give the project a name and replace the following script in it.

function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu(‘XYZ Menu’)
.addItem(‘Generate PO’, ‘createDcument’)
.addToUi();
}

function createDcument() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
var activeRange = activeSheet.getActiveRange();
var row_data = activeRange.getDisplayValues();
var scriptProperties = PropertiesService.getScriptProperties();
var template_id = scriptProperties.getProperty(‘template_id’); // document template

/***Modify indexs if you the spread sheet columns shuffle **/
var attributes = {
bill_no: “0”,
vendor: “1” ,
date: “7” ,
description: “2” ,
length: “3” ,
width: “4”,
height: “5”,
weight: “6”
}
/****************/
var documentId = DriveApp.getFileById(template_id).makeCopy().getId();
DriveApp.getFileById(documentId).setName(‘Purchase Order PO# ‘+ row_data[0][0]);
var body = DocumentApp.openById(documentId).getBody();

for (var prop in attributes) {
var index = attributes[prop];
var propValue = row_data[0][index];
propValue = propValue ? propValue : “”;
body.replaceText(‘##’+prop+’##’, propValue);
}
}

You can find the script here also.

Settings:

I made the template_id as a script variable which can be set from File –> Project Properties so that you dont need to modify the script again.

script properties

Last thing you need to do is add Sheets API and Drive API by going into Resources –> Advanced Google services and also dont forget them to enable in Console.

advanced google services

phew! all the hard work is done sit back and refresh your sheet. You should be seeing the new menu XYZ Menu —> Generate PO

new menu from app script

Click on it (for first time it asks for permissions. grant it). Go to you template folder and you should see the generated document with the bill no included in the file name.

document generated

Hope this will save you sometime. Ping me with any concerns/ comments.

adiós!

Rails migrations best practices

Avoid using Rails models in migration.

Reasons:

  1. Models might have some lifecycle callbacks which we dont want to be invoked in a migration.
  2. As we know what we are doing we dont want to deal with validations.
  3. Model names can change irrespective of db table.
  4. Try to use raw sql as close as possible.

Squash them after a period of time.

Reasons:

  1.  To my experience over a period of time these migration files are painful and useless.
  2. Every rake db:migrate might take a few seconds, or creating of a new database might take a few minutes when these files are big. Consider using tools like squasher.

Make them irreversible.

Reason:

In Production it makes sense to have IrreversibleMigration as the code is tested in lower environments. If there is an issue try to fix it with a follow up migration.

Note: If there is a new developer in your team and setting up their environment. Let them do `rake db:schema:load` to get latest db copy.

Rails after_validation or before_save

I dont think i used after_validation ever as before_save(or similar callbacks like before_create or update) is the one which fits. Usually the flow is

Screen Shot 2018-08-03 at 1.45.57 PM

before_save seems to be the last step in the process and by the time it reaches there the record is valid. Like you wanted it to happen every time when validation is successful.

after_validation is called irrespective of the validation failed or not. I cant think of a scenario where you want to use it.

We cannot substitute after_validation for before_save as we can not guarantee that the record is always going to be valid.

Git merge vs rebase

There is no hard and fast rule. But i am putting out when to use what based on my experience.

Merge if

  • you are in a big team working on a feature branch and you want to rebased from master. If you dont have all the commits you are in trouble.
  • When you find rebasing is becoming painful for no reason. like for every rebase step you need to fix the same lines of code again and again.
  • Not worried about one more additional commit and order of commits as they are going to be mixed up.

Rebase if

  • you dont want to change the commit history/log.
  • You want the commit history looks clean. As new work will be on top of everything.
  • when you have less code changes or you know how to deal with conflicts (more patience required practice meditation).
  • you branched off of feature branch to your own branch. You can do what ever you want as its your world.

ActiveRecord::EagerLoadPolymorphicError: Cannot eagerly load the polymorphic association

 

If you have encountered this error and wondering how to fix it this post is for you.

Rails raises this error when it tries to eager load a polymorphic association using a JOIN.

For example i have a Booking which belongs_to polymorphic association order. An Order can be a PickupOrder or DeliveryOrder

class Booking < ApplicationRecord
  belongs_to :order, polymorphic: true
end
class PickupOrder < ApplicationRecord
  has_many :bookings, as: :order, dependent: :destroy, inverse_of: :order
end
class DeliveryOrder < ApplicationRecord
  has_many :bookings, as: :order, dependent: :destroy, inverse_of: :order
end

I can load all bookings along with orders like this

Booking.includes(:order)

Now if I want to search list of bookings by `order_name` like `ja`

Booking.includes(:order).references(:order).where("order.order_name ilike %ja%")

Throws the error

​​ActiveRecord::EagerLoadPolymorphicError: Cannot eagerly load the polymorphic association :order

Solution: Do it in sql. with left outer joins.

Booking.joins("left outer join delivery_orders on delivery_orders.id = bookings.order_id and bookings.order_type='DeliveryOrder'").
        joins("left outer join pickup_orders on pickup_orders.id = bookings.order_id and bookings.order_type='PickupOrder'").
        where("pickup_orders.order_name like '%ja%' or delivery_orders.order_name like '%ja%'")

Happy coding!

jsoapi-resources belongs_to not implemented?

Have you ever encountered this error while working on jsoapi-resources ?

`DEPRECATION WARNING: In XXX class you exposed a `has_one` relationship using the `belongs_to` class method. We think `has_one` is more appropriate. If you know what you’re doing, and don’t want to see this warning again, override the `belongs_to` class method on your resource.

If yes this post is for you. Dont break your heads its on `​jsonapi-resources`.

It looks like jsoapi-resources did not implement the `​belongs_to` as how it should be. At least as of this writing (0.9.0v). It treats `belongs_to` and `has_one` relations in the way. here is the source which points to this. So for time being change your `belongs_to` to `has_one` and you are gold. Happy coding!