(Novice) Is it possible to reduce the number of body.replaceText() that I'm using in my script?

[Earl Mikko Molina]

I'm new to App Script and I have successfully followed a YouTube tutorial on how to create PDFs from Spreadsheet data and a Docs template.

The tutorial showed how to get the data from Sheets using .getValues() and then using the data taken from Sheets to change the placeholder texts in the Docs template with body.replaceText(). I was successful in making the document update the placeholder texts but my script has a lot of lines for the .getValues() and a lot of matching body.replaceText().

I was wondering if there is a way to reduce the or sort of like "compress" (I don't know if that is the correct term) the .getValues() and replaceTexts()? I have tried searching for answers in Google and StackOverflow as suggested in this community but I'm having a hard time finding answers.

I would really appreciate getting help to solve my concern. Thank you!

[Ed Sambuco]

Your question is vaguely put..it would help if you showed us the script.

But a couple posible assist:

- Put entire shets into a single array so as to minimize getlues calls

- Structure the script into functions, with one function proce@ing replaceText in Doc.  Function may be invoked many times, but there will be only one replaceText.

--
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-community+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/17e7920c-e3dc-4daa-9a50-bb417b4ab768n%40googlegroups.com.

[Earl Mikko Molina]

Hi!

Thank you for responding. I apologize for not putting the script in my original post. So what I have is the data in Google Sheet in a table like this:

and the script that I'm using is getting the data from the table to transfer to the Google Docs template that I made. It looks like this:

const employee_name = ss.getRange("A2").getValues();

        const ticket_number = ss.getRange("B2").getValues();

        const work_type = ss.getRange("C2").getValues();

        const audit_date = ss.getRange("D2").getValues();

        const client_name = ss.getRange("E2").getValues();

        const auditor_assigned = ss.getRange("F2").getValues();

I then have the following edit the Google Doc template:

  const tempFile = docFile.makeCopy(tempFolder);

  const tempDocfile = DocumentApp.openById(tempFile.getId());

  const body = tempDocfile.getBody();

        body.replaceText("{employee_name}",employee_name);

        body.replaceText("{ticket_number}",ticket_number);

        body.replaceText("{work_type}",work_type);

        body.replaceText("{audit_date}",audit_date);

        body.replaceText("{client_name}",client_name);

        body.replaceText("{auditor_assigned}",auditor_assigned);

tempDocfile.saveAndClose();

There are 70 columns in the table where I'm getting the data and it makes it a bit hard to type all the getValues and body.replaceTexts. I am wondering if it's possible for the multiple lines of getValues(); and body.replaceText(); to be shortened or "compressed"? 

[esamb...@gmail.com]

You need to use arrays  Copy the whole of the header row into an array, and the whole of the values row into another array:

Assuming you have a spreadsheet object ss and a document object doc copied from your template, and assuming template text to be replaced is encased in open/close brackets {} (and if I read the structure of your spreadsheet correctly):

const headerArray = ss.getRange(1,1,1,ss.getLastColumn());

const valuesArray = ss.getRange(2,1,1,ss.getLastColumn());

for (i = 0; i < valuesArray.length; i++) {

     doc.replaceText('{' + headerArray[i] + '}', valuesArray[i])

}

That should be about it ...

[Ed Sambuco]

OOPS .. for got the getValues() at the tail end of the array definitions, chained after the getRange ... sorry, elemental goof.

--

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/78f90492-ed2f-412b-bcbf-480e5cf9c9b8n%40googlegroups.com.

[Mikko M]

Thanks so much! I was able to make it work with your code. From over 100 lines to just below 40.

If it's not much of a bother, can you point me to some readings about this specific part?

for (i = 0; i < valuesArray.length; i++) {

     doc.replaceText('{' + headerArray[i] + '}', valuesArray[i])

}

I want to understand how it works and I want to see other usage examples. Thank you!

[Ed Sambuco]

OK Miko, glad to have helped.

For javascript and any of its variants like google app  script, you need to be really familiar with arrays and looping mechanisms.  (The same holds true for PHP,.)  Look up the javascript for loop on google, or ask chatGPT.  You should examine looping through objects, through arrays, etc.  Also take a look at the forEach method, which is more advanced and requires so-called anonymous functions.

I find that converting google spreadsheet ranges to arrays to be convenient and efficient.  In appscript, the getValues method converts the sheet range into a two-dimensional array of row/column data. This array has the number of rows in the range as the number of elements, or array length.  Each row element is in turn an array of all the columnar data in that row.   Hence, you go through two "dimensions" to get the value in a particular row/column, but you use straight array indexing, starting from 0.

SO ..the for loop I showed you loops through both arrays ... the rows (in this case, only one, and then within each row, it picks the i-th column (column A havin index 0, and so on.) to grab the key and the value for each combination you are looking for.

I do confess that I still didn't have everything totally correct for you .. even if the sheet range has only one row, and getValue returns only one array element, that array element is a list of all the column data, so you need to reference it that way.  Here is a good way to do this:

const outArray = ss.getRange(1,1,2,ss.getLastColumn()),getValues();.

for (i = 0; i < outArray.length; i++) {

     doc.replaceText('{' + outArray[0][i] + '}', outArray[1][i])

}

Note that I collapsed this to one array with two elements, the first array element (outArray[0] is an array of the header row column data, and the second element (outArray[1]) is an array of the values attached to the header.  The bracketed [i] loops through all the columns.  There is a double subscripting for the two dimensional array.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6a6ecdcb-3f2f-4623-85d4-4d9e19f186ban%40googlegroups.com.