CODEWITHSUNDEEP
google-apps-script
Chris Eastup
Chris Eastup

Reputation: 1

Ignore Empty Cells

I wonder if someone could help me.

I have set up the script coding below to generate a Google Doc Template based on data in a Google Sheet. The issue I'm having is that when I run the AutoFill Docs process within google sheets, it's creating the letter and adding spaces in the letter for any cells that are empty.

So for example, I have 4 headings (Address 1, Address 2, Address 3 and Address 4) based on the customer's details not all of these are filled.

When the letter/ google doc is generated, it's putting spaces in for the empty cells (See below)

Bob Smith 32 Bob Road

Bobbington

B23 4YH

for empty cells, I need it to ignore these and generate the letter as below:

Bob Smith 32 Bob Road Bobbington B23 4YH

Any help would be much appreciated.

Thanks

Chris

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();
  
}

function createNewGoogleDocs() {

  const googleDocTemplate = DriveApp.getFileById('1oa-BtAhFKsvJ37Dl26Xvt2QTe9HjSz8vE9YVhtXjPA8');
  const destinationFolder = DriveApp.getFolderById('1kPVjBvCrdtb6k3bafl-g-s64b40-oH6I')
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daysavers')
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index){
  if (index === 0) return;
  if (row[10]) return;

  const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[2]} Customer Details` , destinationFolder)
  const doc = DocumentApp.openById(copy.getId())
  const body = doc.getBody();

  body.replaceText('{{Case Reference}}', row[0]);
  body.replaceText('{{First Name}}', row[1]);
  body.replaceText('{{Last Name}}', row[2]);
  body.replaceText('{{Address 1}}', row[3]);
  body.replaceText('{{Address 2}}', row[4]);
  body.replaceText('{{Address 3}}', row[5]);
  body.replaceText('{{Address 4}}', row[6]);
  body.replaceText('{{Postcode}}', row[7]);
  body.replaceText('{{First Name 2}}', row[8]);
  body.replaceText('{{Number}}', row[9]);

  doc.saveAndClose();
  const url = doc.getUrl();
  sheet.getRange(index + 1, 11).setValue(url)

  })

}

Upvotes: 0

Views: 582

Answers (2)

Aaron Dunigan AtLee
Aaron Dunigan AtLee

Reputation: 2072

If the row of the spreadsheet always holds the data you want to insert, in the correct order, you could avoid the multiple placeholders in your template and just put a single placeholder, then replace all of your body.replaceText(...) lines with a single line:

body.replaceText('{{Single Placeholder}}', row.filter(Boolean).join(' '));

row.filter(Boolean) produces an array with only the cell values that have non-empty strings or non-zero number values, and .join(' ') joins them all into one space-separated string.

If you still get the line breaks, it's possible there are newline characters in your spreadsheet cells. Eliminate them with .replace(/\n/g,'')

body.replaceText('{{Single Placeholder}}', row.filter(Boolean).join(' ').replace(/\n/g,'');

Upvotes: 0

idfurw
idfurw

Reputation: 5862

I guess it is not the script putting space, but just the space between the address items leaving there.

body.replaceText(' {{Address 2}}', row[4] ? ' ' + row[4] : '');

Do this with 3 and 4 also

Upvotes: 1

Related Questions