Script that prints style from google spreadsheet to google doc

Question

Please see my spreadsheet.

https://docs.google.com/a/zigmens.com/spreadsheet/ccc?key=0AtXr7M_f8JAadG1qclprdXRFb3VuNXV0OXg5bDNpNkE&usp=drive_web#gid=0

I have a menu item, Create/Job Scope that pulls data from the spreadsheet and creates a new document in "my drive" location of google drive. Problem I have, is that it's not including the stye and format that I have in the spreadsheet. I have tried many ways of setting the variable in the script to "bold", for example, but can't seem to get it to work. I am hoping there is an object that I can add to the variable that will set it's style.

For example, in my script, I have a variable called "desc"

var descr = values[n][3] ;

I would like to just create a new variable such as

var descr = values[n][3] ;

var desc_style = descr.setStyle("bold") ;

Here is the script from the spreadsheet but I think it's best if you just update the script in my spreadsheet.

function jobScope() {

  var ss = SpreadsheetApp.getActiveSheet();
  var values = ss.getDataRange().getValues();
  var docTemplate = "19ANrZluvbavWU4Ttgh1z9_DVJgEQ1hrGohd4lQAg7vI";
  var job_name = ss.getRange("D4").getValue();
  var docName = job_name+' Job Scope '; 

  var x = 1 ;
  while(values[x][0] ^= "") {    
     ++x ;
  }
  var textToDoc = "" ;
  for(n=1;n<x;++n){  
     var cell = values[n][4] ; 
    if (cell ^ "0") {
      var line_item = values[n][1]; 
      var descr = values[n][3] ;
      textToDoc = textToDoc + line_item + "  " + descr + "\n\n" ;

    }

  }

  var copyId = DocsList.getFileById(docTemplate)
  .makeCopy(docName)
  .getId();  

  var copyDoc = DocumentApp.openById(copyId);

  var copyBody = copyDoc.getActiveSection();

  copyBody.replaceText('keyScope', textToDoc);
  copyBody.replaceText('keyJobName', job_name);

  copyDoc.saveAndClose();  

}

You need to also give permission for the document template (19ANrZluvbavWU4Ttgh1z9_DVJgEQ1hrGohd4lQAg7vI). We need to be able to access that document to work with your script (giving a permission error since it's not set to public). — thoughtcrime
I made the document template public. Sorry about the delay. I expected an email notification, but I guess that didn't happen. I hope someone can help me with this today. — zigmens
I wonder if I need to use and html script so that I can use the html tags for styling the document. So when I have the script editor open, should I create a new "html" file (File/New/HTML file). Problem is I don't know how to go down this road... — zigmens

thoughtcrime thoughtcrime · Accepted Answer · 2014-03-07T02:01:55

Have you tried setting the formatting of the keys to your preferred style? If you format the key (ie keyScope) in the document template, then the variable that replaces the key will retain they key's formatting.

  //...trimmed previous code
  // Get document template, copy it as a new temp doc, and save the docs id
  var copyId = DocsList.getFileById(docTemplate)
  .makeCopy(docName)
  .getId();  

  //Open the temporary document and set it to a variable
  var copyDoc = DocumentApp.openById(copyId);

  //Get the documents body section
  var copyBody = copyDoc.getActiveSection();

  //Replace place holder keys, in our google doc template
  copyBody.replaceText('keyline_item', line_item);
  copyBody.replaceText('keyDescr', descr);
  copyBody.replaceText('keyJobName', job_name);

  copyDoc.saveAndClose();



}

Although this is the only answer I know of that answers the original question, what the author is looking for is a way to preserve formatting (bold) in a variable that joins two values from different cells (with different formatting). To the best of my knowledge, it is not possible to carry formatting from spreadsheets to documents, regardless of the number of variables because the keyReplace object is only composed of the raw text (or formula) that occupies the targeted cell or range where .getValues() is used.

If I'm wrong about this, I would appreciate someone correcting me, but since the author combines two adjacent cells into a single variable, and that is the answer he is seeking, I would think that it still wouldn't be possible to do as a single keyObject replacement in a template because two cells with different formatting are being merged into a single object in the script. I know that you can get background and foreground colors, but you have to get those by R1C1 notation. Are there other formatting options that can be captured from spreadsheets?

Either way, I'm going to leave this to the pros, because the only way I could see, from his source files, was to completely rewrite his document template and script, and that is not what the author of the question is looking for. I will leave my script above for people who may want to do something similar in the future.

Script that prints style from google spreadsheet to google doc

1 Answers