Adding to a Google Doc from a Google Spreadsheet Using Apps Script

Question

I'm new to using the DocumentApp in Google Apps, so looking for some help!

I'm trying to create an FAQ (in a Google Doc) automatically from a spreadsheet. If certain conditions are met in the spreadsheet row, I want the script to find the category of the question in the document and insert a new question and response underneath it (pushing anything already there back).

So I'd have a document that looks like this:

https://docs.google.com/document/d/1fjb3RO6hUY6n7x0bu6WvtcleMWRNC8VQ9U82hXiGqcY/edit?usp=sharing

And a spreadsheet that looks like this:

https://docs.google.com/spreadsheets/d/1fb3ceqP6142_C7QQ1PfkWtVNswOyzOxkWCofvauf4Ps/edit?usp=sharing

And this is the code I'm trying to use. I'm getting a ton of errors- mainly because I don't have a good grasp on what the different elements involved are. Can anyone point me in the right direction?

function insertnewquestion() {

//(works fine)Get active document and related spreadsheet  
var doc = DocumentApp.getActiveDocument().getBody();
var ss = SpreadsheetApp.openById("xxxxxxx");
var sheet = ss.getSheetByName("xxxxx");

//(works fine)Go through the various rows in the spreadsheet up to the last row  
for (var row = 2; row <= sheet.getLastRow(); ++row) {
var status = sheet.getRange(row,4).getValue();
var question = sheet.getRange(row,1).getValue();
var response = sheet.getRange(row,2).getValue();
var category = sheet.getRange(row,3).getValue();
var date = sheet.getRange(row,5).getValue();

//(works fine)find rows with blank status and a response filled in    
if (status !== "Entered" && response !== "") {

//(errors! this is where i need help) find the pertinent header  

var categoryposition = body.findText(category);  //looking for the category header- new text should be added after this, the idea is that what was already under this header will move down
var questionheader = categoryposition.appendText(question);  //trying here to add in question text after where the category was found
questionheader.setHeading(DocumentApp.ParagraphHeading.HEADING3); //set question text as heading 3 (so it shows up on table of contents)
questionheader.appendText("\r"+"\r"+response+"\r\r"); //add line breaks and then the response text in normal font, then more line breaks to put space between new stuff and old stuff

//(works fine)Mark in the spreadsheet that it was entered in FAQ and the date so that it isn't double entered next time the script runs

sheet.getRange(row,4).setValue("Entered");
var currentTime = new Date()
var month = currentTime.getMonth() + 1
var day = currentTime.getDate()
var year = currentTime.getFullYear()
var date = (month + "/" + day + "/" + year) 

sheet.getRange(row,5).setValue(date);

}
  else {continue}


}

//(need help!) Refresh table of contents to include new questions

//no idea how to do this!

}

KRR KRR · Accepted Answer · 2015-04-20T23:29:47

In the code you are referring to body.findText(category); where it should be doc.findText(category);. Also for the line:

var categoryposition = body.findText(category);

it returns the RangeElement — a search result indicating the position of the search text, or null if there is no match

Before adding any lines of code you have to check for null value in categoryposition.

The text class has a method to insert text in particular offset value to particular String value gives as shown here.

Hope that is helpful.

Adding to a Google Doc from a Google Spreadsheet Using Apps Script

1 Answers