Doc Template Created by Sheet: Help with URL insertion

[Darrell Ellis]

Thank you in advance to anyone willing to take a look at this. 

Problem: I cannot figure out the script that will use a Sheet to create docs from a Doc Template that inserts clickable url.'s. 

Background: 

I used Mr. Jeff's tutorial as a starting point (and it works for all the text). 

I have tried to solve based on his advice to someone with a similar request on his web site (#27). Where he suggested to add a Find Text then use part of the answer from this Stacked Overflow to change the text to a url. 

- I found another close answer her by Phil Bainbridge, but his version also only inserts text from the sheet to the docs created by the Doc Template. 

Help Needed: 

I would be very appreciate if someone could look at this sheet and this doc template, then provide the script that would insert the data from Columns E2:E and G2:G, not as text (which is presently does) but insert those fields as clickable web links. 

Thank you for any help/advice. 

Here is the script that I got from Mr. Jeff (Thanks Mr. Jeff!)

______________________________________________________________________________

function createNewGoogleDocs() {

//This value should be the id of your document template that we created in the last step

const googleDocTemplate = DriveApp.getFileById('This.Is.My.File.ID');

//This value should be the id of the folder where you want your completed documents stored

const destinationFolder = DriveApp.getFolderById('1This.Is.My.Folder.ID')

//Here we store the sheet as a variable

const sheet = SpreadsheetApp

.getActiveSpreadsheet()

.getSheetByName('Data')

//Now we get all of the values as a 2D array

const rows = sheet.getDataRange().getValues();

//Start processing each spreadsheet row

rows.forEach(function(row, index){

//Here we check if this row is the headers, if so we skip it

if (index === 0) return;

//Here we check if a document has already been generated by looking at 'Document Link', if so we skip it

if (row[7]) return;

//Using the row data in a template literal, we make a copy of our template document in our destinationFolder

const copy = googleDocTemplate.makeCopy(`${row[0]} Weekly News` , destinationFolder)

//Once we have the copy, we then open it using the DocumentApp

const doc = DocumentApp.openById(copy.getId())

//All of the content lives in the body, so we get that for editing

const body = doc.getBody();

//In these lines, we replace our replacement tokens with values from our spreadsheet row

body.replaceText('{{Week Begin}}', row[1]);

body.replaceText('{{Week End}}', row[2]);

body.replaceText('{{Instructional Title}}', row[3]);

body.replaceText('{{Instructional Message}}', row[4]);

body.replaceText('{{Administrative Title}}', row[5]);

body.replaceText('{{Administrative Message}}', row[6]);

//We make our changes permanent by saving and closing the document

doc.saveAndClose();

//Store the url of our new document in a variable

const url = doc.getUrl();

//Write that value back to the 'Document Link' column in the spreadsheet.

sheet.getRange(index + 1, 8).setValue(url)

})

} 

[Clark Lind]

On the last line of your code, try simply adding ".setShowHyperlink(TRUE)"

sheet.getRange(index + 1, 8).setValue(url).setShowHyperlink(TRUE). You may need to play with it a little to get the syntax correct.

[Andrew Roberts]

Or try:

sheet.getRange(index + 1, 8).setValue('=HYPERLINK("' + url + '", "' + message + '")')

(message could just be the URL itself)

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e8eae8d7-733d-498d-8659-77665087b680n%40googlegroups.com.

[Darrell Ellis]

Thanks for taking a look; however, I think you misunderstood my problem, or I am unable to apply your solutions. 

It appears that both of your  return the URL to the spreadsheet. The original script did that, and I was fine with the way it appeared (as a full url)

What I was looking for was the links listed on the Spreadsheet, which had all of the data used to make the documents from the doc template, to be put in as clickable links

That line in the Doc, comes from Cell E2 on the spreadsheet, but it is a clickable link on the spreadsheet. But on the created DOC it is just text. 

Does that make sense. 

Thanks

Darrell 

[Andrew Roberts]

Have you seen this in SO.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a3bfa433-fe7f-4a29-8265-35664e4aef31n%40googlegroups.com.

[Darrell Ellis]

Yes sir,

I have used that SO script example by itself and gotten it to work; however,  I am not experienced (or intelligent) enough to figure out how to get this to work within this present situation. 
DE

[Martin Molloy]

In your spreadsheet script  I added the following after line 39. It worked for me

Martin

    var linkURL4 = sheet.getRange(index+1,5).getRichTextValue().getLinkUrl()  //index + 1 is the row number. 5 is column E
    var linkURL6 = sheet.getRange(index+1,7).getRichTextValue().getLinkUrl()  // 7 is column G
    if(linkURL4){
    body.findText('{{Instructional Message}}').getElement().asText().setLinkUrl(linkURL4);
    }
    if(linkURL6){
    body.findText('{{Administrative Message}}').getElement().asText().setLinkUrl(linkURL6);
    }

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bfd13c19-5703-433b-a189-a8e6f2558573n%40googlegroups.com.

[Darrell Ellis]

Mr. Molloy,

This was excellent. Worked like a charm. You are a genius!

Thank you so much for taking the time to look at this and solve it.

 

Thank you