Create Printable Bid from spreadsheet data

[cbird02]

I have a highly customized spreadsheet that I use in the field on my Android Tablet for estimating jobs. It works great. I want to take the next step to being able to print the bid onsite during the appointment. I need advice on what functions I should use and how to structure the overall application. I will refer to 2 sheets: Sheet 1 = Bid calculations, Sheet 2 = Bid output

SHEET 1 - the scopes of work for my bids are different for each job. I have about 10 different measures that we bid. So I have rows for each measure, but each bid has a different combination of measures. So on Sheet 1, I may have bid amounts for Measure 1,4,6,7,9 but not for the other measures. I would like to place a "marker" next top each measure that is to be performed and use that marker to tell SHEET 2 what to import and print.

SHEET 2 - This is the sheet I need to create which I will attempt to make a printable bid. I need to pull data from measures that I mark to be incorporated into the bid. For instance, if I need the bid to print Measure 4,6,7 (with brackets used for imported data from SHEET 1)

SHEET 2

ROW 1 [Measure 4 Description] [Measure 4 Amount]
ROW 2 [Measure 6 Description] [Measure 6 Amount]
ROW 3 [Measure 7 Description] [Measure 7 Amount]
ROW 4 Blank
ROW 5 Blank

​

Now the difficult part is that I don't want a bunch of empty spaces for measures that I am not performing. So, the logic of ROW 1 formula needs to say "import Measure 1 data, unless blank, then import Measure 2 data, unless blank, then import Measure 3 data.....etc AND ROW 2 needs to do the same thing, except if ROW 1 already imported Measure 4 data, it knows to skip to the next specified Measure.

Your help is appreciated.

 

[p45cal]

I can download, and yes it's merged cells causing the problem. I can work round merged cells but it adds a significant degree of complexity to the code. Is there any way that we can lose the merged cells? In Excel you can just widen columns, is this not possible in Google sheets?

In this file, I've added a Sheet1b which is a copy of your Sheet1 but with cells unmerged and a few columns hidden (I don't know whether it'll be easy for you to adjust that sheet so I've stuck with the same columns, but hidden a few).
On sheet3 is a button which calls blah.
Again, it can be tweaked.

There are three chunks of very similar code which means they can be reduced to just one chunk but put in a loop which goes round three times. This makes it easier to make adjustments but comes at the cost of code readability/understanding. Each chunk of code is quite long because they also add the highlighting and formatting that you have in Sheet2, as well as ensuring that the three resultant sections are equidistant vertically from each other, regardless of how many rows there are in each section (and if there are no items in any section, that section isn't output at all).

 

[cbird02]

I can easily get rid of the merged cells. Right now, the bid data you see, is just cut from my main data entry sheet. On the data entry sheet, merged cells are important. I can just create a sheet #2 that just has the bid numbers calculated from the data entry sheet. Then Sheet #3 will be my printable sheet.

I have a question - Can I have a pre-formatted Sheet #3, and when the script is run, it just enters the number into the already formatted cell? I am going to really need to tweek sheet #3 since it will be what is printed for the customer. I would rather just have to change the reference cell in the code rather than all the formatting.

THanks so much for your help!

 

[p45cal]

I can just create a sheet #2 that just has the bid numbers calculated from the data entry sheet.

You certainly could, and it could be hidden too.
But why are the merged cells so important? You can do something similar with alignment and centre-across-selection in cells|formatting.

Can I have a pre-formatted Sheet #3, and when the script is run, it just enters the number into the already formatted cell?

Yes of course but… since you're going to have varying numbers of rows for each section, cells with different formatting such as the title of each section and the totals cells are going to be at different points (on different rows at different times), so it is difficult to see how you can pre-format the cells.

 

[cbird02]

You certainly could, and it could be hidden too.
But why are the merged cells so important? You can do something similar with alignment and centre-across-selection in cells|formatting.

I have sections for different types of work that are stacked on top of each other with some fields that need 1 character entry and some are blocks of text, so I can't have uniform column widths. Just spent way to much time to perfect it to change it now. Would rather just have a Sheet2

Yes of course but… since you're going to have varying numbers of rows for each section, cells with different formatting such as the title of each section and the totals cells are going to be at different points (on different rows at different times), so it is difficult to see how you can pre-format the cells.

I do know that each section will not go over a certain amount of rows and I am ok with having a fixed number of rows in each section with Blanks for rows not in use, creating spaces between sections. Would that work?

 

[p45cal]

I've updated the file here.
It contains the previous version still, but on Sheet5 there are two buttons, both do the same thing, using a hidden intermediary Sheet4 as you suggested. Ultimately the source of Sheet5's results is your original Sheet1.
blah2 uses 3 chunks of similar code, while blah3 does the same using a loop. Now you can format Sheet5 and the formats should be retained. There are three SUM() formulae on Sheet5.