CODEWITHSUNDEEP
javascriptgoogle-apps-script
smatthewenglish
smatthewenglish

Reputation: 2889

Reading from Google Sheets and writing content to Google Doc using app script

I have a table that looks as follows:

enter image description here

I'd like to read that data from my Google Sheet, and then write it to a Google Doc that says something along the lines of 

Hi James Madison, Welcome to Vienna!
Hi James Monroe, Welcome to Toronto!
Hi John Quincy Adams, Welcome to Paris!
Hi Andrew Jackson, Welcome to New York! 
Hi Martin Van Buren, Welcome to London!
Hi William Henry Harrison, Welcome to Tokyo!
Hi John Tyler, Welcome to Berlin!

For this I know I need to create a script and also a template.

Each one of those sentences should exist on it's own in a new document, so they should all be collected in the same folder.

Finally I'd like to send all the documents out by email.

I've been looking over all the different examples out there, mail merge and the like, but even after many hours of searching and fiddling I've still not been able to figure out how to make such a script.

Perhaps someone with some more experience than I might be able to show me how to do it or point me to a resource that would explain it in a clear and comprehensible way.

Here's my first naive attempt:

function extractDataFromSheet() {

  // Opens spreadsheet by its ID
  var spreadsheet = SpreadsheetApp.openById("1ow7iuLoqfCSqVhp3ra2uy-22LHUJfQA2UpZgOvIuQ5c");

  // Get the name of this SS
  var name_of_sheet = spreadsheet.getName(); 

  var sheet = spreadsheet.getSheetByName('PussyCow'); // or whatever is the name of the sheet 
  var range = sheet.getRange(1,1); 
  var data = range.getValue();
  //Browser.msgBox(name_of_sheet +" "+data);

  // Opens doc by its ID
  var doc = DocumentApp.openById("1ZSqN-OOxvDV9YLpuDsXhqSWx4X3djUaVehuIlZbanxo");

  var name_of_doc = doc.getName();

  var doc_body = doc.getBody();

 // Use editAsText to obtain a single text element containing
 // all the characters in the document.
 var text = doc_body.editAsText();

 // Insert text at the beginning of the document.
 text.insertText(0, data);

 // Insert text at the end of the document.
 text.appendText(name_of_sheet);

 // Make the first half of the document blue.
 text.setForegroundColor(0, text.getText().length / 2, '#00FFFF');


 Browser.msgBox(name_of_doc);
}

Upvotes: 0

Views: 424

Answers (1)

Eric Swedlo
Eric Swedlo

Reputation: 46

I'm not going to write the entire script for you, but i'll show you an example of what you can do in paraphrased code (hopefully that makes sense).

So, the first thing you would want to do is collect all the data. A good way to do this would be something like:

var ss = SpreadsheetApp.getActive()
var dataList = []
var iteration = ss.getLastRow()
while ( iteration > 0 ) {
    dataList.push(ss.getRange('A' + iteration).getValue() + ',' + ss.getRange('B' + iteration).getValue())
    iteration--
}

At this point, you have a list of all your data. Next, we need to make all this info into seperate google docs. This can be done with something like:

var documentList = []
for (i=0;i<dataList.length;i++) {
    documentList.push(DocumentApp.create('documentName'))
}
//this is DEFINITELY not the fastest or most effecient way to do it, but whatever
var body = undefined
for (i=0;i<documentList.length;i++) {
    body = 'Hi ' + dataList[i].split(',')[0] + ', Welcome to' + dataList[i].split(',')[1] + '!'
    documentList[i].getBody.appendParagraph(body)
}

Then, we want to email people. Your table doesn't track emails, so this is the best I can do:

 for (i=0;i<documentList.length;i++) {
     MailApp.sendEmail('emailAdress','subject','whatever you want the body to be')
 }

Upvotes: 1

Related Questions