Google Script reformat getValues result

Question

I have a small Google script in sheets that grabs a row of data and then I email it. So far its working fine, but I would really like to reformat the body message to look cleaner.

var bodyData = e.source.getActiveSheet().getRange(row, 1, 1, 8).getValues();

bodyData is what i'm sending, which right now is a comma separated line of text:

123456,Driver,Destination,BOL,80,Fri Jul 17 2020 15:46:58 GMT-0500 (Central Daylight Time),Fri Jul 17 2020 15:47:18 GMT-0500 (Central Daylight Time),Test

And this works fine, but I would like to reformat that and clean it up have a string like:

PickUp Number: 123456

Driver: Driver

Destination: Destination

Bill of Lading: BOL

Tons: 80

Date Entered: Fri Jul 17 2020 15:46:58 GMT-0500 (Central Daylight Time)

Laste Changed: Fri Jul 17 2020 15:47:19 GMT-0500 (Central Daylight Time)

Note: Test

Without the extra blank line I had to put in there to keep it from merging everything together in this post. Any assistance with this matter is greatly appreciated!

dwmorrin dwmorrin · Accepted Answer · 2020-07-17T22:02:28

If you just want to transform comma separated values (csv) string to a formatted string, you can split the csv string with the string method split(","), which returns an array of values, which you can format however you like. Example given below. However, since you can send an HTML email body, consider using the HTMLService to format the values with an HTML template.

// usage: var desiredString = csvToString(bodyData);

function csvToString(csv) {
  const values = csv.split(",");
  return `Pickup Number: ${values[0]}
Driver: ${values[1]}
Destination: ${values[2]}
Bill of Landing: ${values[3]}
Tons: ${values[3]}
Date Entered: ${values[4]}
Last Changed: ${values[5]}
Note: ${values[6]}`;
}

Google Script reformat getValues result

1 Answers