Google Sheets - Script to Generate Paragraph in a Column

Question

I've found a LOT of scripts that will take data from a Google Sheet and create a Google Doc, but I need a paragraph (really just a few sentences) generated into the Sheet itself. We create web pages for people who enter their information into a Google Form (name of their store, location, brands carried, etc.), we do this by exporting the Google Sheet into a .csv file, and uploading the file which generates the pages. So we need the paragraph to be in the Google Sheet.

I need a script that will give me a paragraph that says:

Column D is a "OUR BRAND" dealer located in Column H. Column D also sells Column T.

I've been trying to figure out this script for hours piecing together what I've found elsewhere and it seems like every time I sort out one bit, I screw up another bit. It's a mess:

function onEdit() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSheet.getSheetByName("All Dealers");  
  var cell = sheet.getActiveCell();  
  var row = cell.getRow();
  var col = cell.getColumn();


    // Fetch the range of cells
    var dataRange = sheet.getRange(row, col)

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var dealerName = row[0]
    var city = row[1]
    var brand = row[2];
    }

  var body = 'dealerName' + "is a OURBRAND located in" + 'city' + 'dealerName' + "also sells" + 'brand';

  SpreadsheetApp.getActiveSheet().getRange("AI"+row).setValue('body');
}

Spreadsheet is available here

Don't put quotes of any kind around variable names: it should be body = dealerName + "is... and so on. Also, this task does not require a script at all. — user3717023

Unknown Unknown · Accepted Answer · 2016-05-26T19:40:51

You don't need a script for this: an arrayformula can do what you want. Preliminary version: in cell AI2, enter

=arrayformula(D2:D & " is a BRAND dealer located in " & H2:H & ". "  & D2:D & " also sells " & T2:T)

(Note that I've put spaces in appropriate places; this is something that requires extra attention when concatenating strings.)

The drawback of the above is that you also get some text in empty rows. To filter them out, replace each column reference by its filtered version: filter( ... , len(D2:D)) keeps only the rows with nonempty column D.

=arrayformula(filter(D2:D, len(D2:D)) & " is a BRAND dealer located in " & filter(H2:H, len(D2:D)) & ". "  & filter(D2:D, len(D2:D)) & " also sells " & filter(T2:T, len(D2:D)))

This being in arrayformula means that as new data is added to columnds D,H,T, new text will be created in column AI.

Google Sheets - Script to Generate Paragraph in a Column

1 Answers