I'm looking to export a logbook of mine to paper. The logbook consists of entries of hours based on dates that continue row by row.
I need to export these entries through printing with each page containing page totals as well as totals carried forward from previous pages on the last row of each page. Each row of hours should have sum of the hours per page. [Mockup of Rough Export formatting][1]
I understand I could split the sheet up manually and sum the inputs that way. Is it possible to automatically format the hours with the rest of my logbook so that I am able to have this continue on as I update my logbook.
Thank you [1]: https://i.sstatic.net/Tpgb48mJ.png
This script will create a new sheet in your spreadsheet called "Print Logbook" (or clear it if it already exists) and populate it with data from your original logbook, automatically adding total rows and formatting them
/**
* @fileoverview This script generates a printable version of the logbook
* with page totals and carried-over totals.
* It reads data from a source sheet and creates a new formatted sheet for printing.
*/
/**
* Main function to generate the printable formatted logbook.
* Reads data from the source sheet, calculates totals, and writes them to a new sheet.
*/
function generatePrintableLogbook() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
// Name of the sheet where your original logbook data is located.
// Modify this value if your sheet name is different.
const sourceSheetName = "Logbook"; // Changed
// Name of the sheet that will be created or updated with the printable version.
const printSheetName = "Printable Logbook"; // Changed
const sourceSheet = ss.getSheetByName(sourceSheetName);
if (!sourceSheet) {
SpreadsheetApp.getUi().alert("Error: Source sheet '" + sourceSheetName + "' not found. Please ensure the name is correct."); // Translated message
return;
}
// Create or get the print sheet. If it exists, clear its content.
let printSheet = ss.getSheetByName(printSheetName);
if (printSheet) {
printSheet.clearContents(); // Clear existing content
} else {
printSheet = ss.insertSheet(printSheetName); // Insert a new sheet
}
// --- Configurations ---
// Number of header rows in your source sheet (e.g., "Date", "Description", "Hours").
const HEADER_ROWS = 1;
// Index of the hours column (0-indexed). If hours are in column C, the index is 2.
const HOURS_COLUMN_INDEX = 2;
// Number of data rows for each printed "page".
// Adjust this value based on how many rows you want on each physical page.
const ROWS_PER_PAGE = 25;
// Number of empty rows to insert between page blocks for visual separation.
const EMPTY_ROWS_BETWEEN_PAGES = 3;
// Get all data from the source sheet
const sourceDataRange = sourceSheet.getDataRange();
const allSourceValues = sourceDataRange.getValues();
// Extract header rows
const headers = allSourceValues.slice(0, HEADER_ROWS);
// Extract actual logbook entries
const logEntries = allSourceValues.slice(HEADER_ROWS);
let outputRows = []; // Array that will hold all rows for the print sheet
let cumulativeHours = 0; // Cumulative total of hours from previous pages
let currentPageData = []; // Data for the current page
let currentPageHours = 0; // Total hours for the current page
let rowsOnCurrentPage = 0; // Counter for data rows on the current page
// Iterate through the logbook entries
for (let i = 0; i < logEntries.length; i++) {
const row = logEntries[i];
// Attempt to convert hours to a number, handling non-numeric values
const hours = parseFloat(row[HOURS_COLUMN_INDEX]);
// If starting a new page block (or it's the very first block)
if (rowsOnCurrentPage === 0) {
if (outputRows.length > 0) { // If not the very first block, add empty rows for separation
for (let j = 0; j < EMPTY_ROWS_BETWEEN_PAGES; j++) {
outputRows.push(new Array(sourceDataRange.getNumColumns()).fill(''));
}
}
outputRows.push(...headers); // Add headers for the new page block
}
// Add the row to the current page's data
currentPageData.push(row);
// Update the total hours for the current page
currentPageHours += isNaN(hours) ? 0 : hours;
rowsOnCurrentPage++; // Increment the counter for rows on the current page
const isLastEntry = (i === logEntries.length - 1); // Check if it's the last entry
// If the page is full or it's the last entry, finalize the current page block
if (rowsOnCurrentPage === ROWS_PER_PAGE || isLastEntry) {
// Add the data rows for the current page to the final output
outputRows.push(...currentPageData);
// Add the "Page Total" row
const pageTotalRow = new Array(sourceDataRange.getNumColumns()).fill('');
pageTotalRow[0] = 'Page Total:'; // Translated text
pageTotalRow[HOURS_COLUMN_INDEX] = currentPageHours;
outputRows.push(pageTotalRow);
// Add the "Carried Over Total" row
const carriedOverTotalRow = new Array(sourceDataRange.getNumColumns()).fill('');
carriedOverTotalRow[0] = 'Carried Over Total:'; // Translated text
// The "Carried Over Total" is the cumulative total of hours *before* this page's hours
carriedOverTotalRow[HOURS_COLUMN_INDEX] = cumulativeHours;
outputRows.push(carriedOverTotalRow);
// Update the cumulative total for the next page
cumulativeHours += currentPageHours;
// Reset counters and data for the next page
currentPageData = [];
currentPageHours = 0;
rowsOnCurrentPage = 0;
}
}
// Write all generated rows to the print sheet
if (outputRows.length > 0) {
printSheet.getRange(1, 1, outputRows.length, outputRows[0].length).setValues(outputRows);
} else {
SpreadsheetApp.getUi().alert("No data found in the source sheet for printing. Please ensure the sheet '" + sourceSheetName + "' contains data."); // Translated message
return;
}
// --- Apply Formatting ---
applyFormatting(printSheet, outputRows.length, sourceDataRange.getNumColumns(), HEADER_ROWS, ROWS_PER_PAGE, EMPTY_ROWS_BETWEEN_PAGES);
SpreadsheetApp.getUi().alert("Logbook formatted for printing in sheet '" + printSheetName + "'."); // Translated message
}
/**
* Applies formatting to the print sheet.
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet to apply formatting to.
* @param {number} totalRows The total number of rows in the print sheet.
* @param {number} totalCols The total number of columns in the print sheet.
* @param {number} headerRows The number of header rows for each page block.
* @param {number} rowsPerPage The number of data rows per page.
* @param {number} emptyRowsBetweenPages The number of empty rows between page blocks.
*/
function applyFormatting(sheet, totalRows, totalCols, headerRows, rowsPerPage, emptyRowsBetweenPages) {
// Set column widths (adjust these values if necessary)
sheet.setColumnWidth(1, 100); // Column A: Date
sheet.setColumnWidth(2, 300); // Column B: Description
sheet.setColumnWidth(3, 80); // Column C: Hours
// Apply bold and borders to header and total rows
let currentOutputRow = 1; // Start from the first row of the output sheet
// Iterate through page blocks to apply formatting
while (currentOutputRow <= totalRows) {
// Apply bold to headers
sheet.getRange(currentOutputRow, 1, headerRows, totalCols).setFontWeight('bold');
currentOutputRow += headerRows; // Move pointer past headers
// Skip data rows (they will be formatted as normal text)
currentOutputRow += rowsPerPage;
// Apply formatting to "Page Total" and "Carried Over Total" rows
if (currentOutputRow <= totalRows) { // Check if "Page Total" row exists
sheet.getRange(currentOutputRow, 1, 1, totalCols).setFontWeight('bold').setBorder(true, true, true, true, false, false);
currentOutputRow++; // Move pointer to the next row
if (currentOutputRow <= totalRows) { // Check if "Carried Over Total" row exists
sheet.getRange(currentOutputRow, 1, 1, totalCols).setFontWeight('bold').setBorder(true, true, true, true, false, false);
currentOutputRow++; // Move pointer to the next row
}
}
// Skip empty separator rows to move to the next page block
currentOutputRow += emptyRowsBetweenPages;
}
}
/**
* Creates a custom menu in Google Sheets to easily run the script.
* This function runs automatically when the Google Sheet is opened.
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Logbook') // Changed from "Diario di Bordo"
.addItem('Generate for Print', 'generatePrintableLogbook') // Translated item name
.addToUi();
}
Code Explanation:
generatePrintableLogbook(): This is the main function.
It defines the names of the source sheet (sourceSheetName) and the print sheet (printSheetName). Ensure that sourceSheetName exactly matches the name of your original logbook sheet.
It defines HEADER_ROWS (how many header rows you have, e.g., "Date", "Description", "Hours"), HOURS_COLUMN_INDEX (the 0-indexed column index for hours, so Column A is 0, Column B is 1, Column C is 2), ROWS_PER_PAGE (how many data rows you want on each simulated page), and EMPTY_ROWS_BETWEEN_PAGES (empty rows to visually separate page blocks).
It reads all data from your original sheet.
It iterates through each data row, accumulating hours for the current page and the cumulative total.
When it reaches the ROWS_PER_PAGE limit or the last row of the logbook, it adds the following to the output sheet:
The headers (for each new page block).
The data rows for the current page.
A "Page Total" row with the sum of hours for that page.
A "Carried Over Total" row with the sum of hours from all previous pages.
Finally, it writes all the formatted data to the printSheetName sheet.
applyFormatting(): This function is responsible for applying the formatting:
It sets the column widths for better readability.
It applies bold font and borders to the header rows, "Page Total" rows, and "Carried Over Total" rows.
onOpen(): This function creates a custom "Logbook" menu in your Google Sheet, with a "Generate for Print" option. This makes it very easy to run the script whenever you need it.
Now, every time you update your original logbook, you just need to run the script to get a print-ready version with all totals calculated and formatted automatically.