Creating a dynamic table in Google Docs from Google Sheets data with specific conditions

Other Questions Google docs
1

Hey everyone, I’m trying to improve my Google Apps Script that makes invoices. It’s almost there, but I’m stuck on the table part. Here’s what I want to do:

  1. Add standard headings to the table
  2. Only include rows that have data in column A
  3. Add an empty column on the right side of the table

Right now, my code looks like this:

var data = sheet.getRange('A1:E50').getDisplayValues();
doc.getBody().appendParagraph('Invoice')
  .setHeading(DocumentApp.ParagraphHeading.HEADING1)
  .setAlignment(DocumentApp.HorizontalAlignment.CENTER);
var table = doc.getBody().appendTable(data);
table.getRow(0).editAsText().setBold(true);

But it’s not quite right. The first cell shows a number instead of ‘Name’, and it includes empty rows. I tried to add an empty column by using ‘A1:E50,S1:S’, but that broke the script.

Any ideas on how to fix this? I’m pretty new to Google Apps Script, so any help would be awesome. Thanks!

2

I’ve been down this road before, and I can share some tips that might help. First, for the headings, you’ll want to create them separately and then add them to your table. Something like:

var headers = ['Name', 'Description', 'Quantity', 'Price', 'Total', ''];
var headerRow = table.insertRowBefore(0);
for (var i = 0; i < headers.length; i++) {
  headerRow.getCell(i).setText(headers[i]).setBackgroundColor('#f3f3f3');
}

For only including rows with data in column A, you can filter your data before creating the table:

var data = sheet.getRange('A1:E50').getValues().filter(row => row[0] !== '');

As for adding an empty column, you can do this after creating the table:

table.insertColumnAfter(table.getNumColumns() - 1);

These changes should get you closer to what you’re aiming for. Let me know if you need any clarification!

3

Having worked with Google Apps Script for invoice generation, I can offer some insights. To address your issues, you’ll need to modify your code a bit. First, create an array for your headers and append it to your data. Then, filter out rows without data in column A. Here’s a snippet to get you started:

var headers = ['Name', 'Description', 'Quantity', 'Price', 'Total', ''];
var data = sheet.getRange('A1:E50').getValues();
var filteredData = data.filter(row => row[0] !== '');
filteredData.unshift(headers);

var table = doc.getBody().appendTable(filteredData);
table.getRow(0).editAsText().setBold(true);

This approach should solve your headings issue and only include rows with data. For the empty column, you can use the insertColumnAfter() method after creating the table. Hope this helps you move forward with your invoice script!

4

hey, i had similar probs. try making an array for headers, filter out empty rows, unshift the headers into your data, append the table and then insert a new column. like:

var headers = ['Name','Description','Quantity','Price','Total',''];
var data = sheet.getRange('A1:E50').getValues().filter(r => r[0]);
data.unshift(headers);
var table = doc.getBody().appendTable(data);
table.insertColumnAfter(5);

hope it works!