Replace Last row from each column into G-Slides Using Appscript

Question

I have a g-sheet where the last row is the sum of the each column rows.

Wish to Achieve : I want to now populate a template Google slide using the last values of each column into placeholders.

Template slide structure is like this

My efforts as of now while writing an app script function is:

function replace_template_with_text_charts() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

// sheet for handlebar placeholders = dataSheetName
var rows = sheet.getDataRange().getDisplayValues();
// Append column for link to generated slide decks at the end of the row
var resultColumn = sheet.getLastColumn() + 1;
sheet.getRange(1, resultColumn).setValue("Generated slide deck");
// Make name2index store a mapping from column names to indexes.
var name2index = {};
var header = rows[0];
for (var i = 0; i < header.length; i++) {
name2index[header[i]] = i;
}
Logger.log(name2index);
Logger.log(rows[sheet.getRange(sheet.getLastRow(), name2index['A'])])
// Get the presentation template file name based on id that was indicated in set up 
 var p1 = SlidesApp.openById('1X3tAszOcjo-QkrRNj3TWFm-_--GKPxdLaoJYp6oHJ7M')

 for (var i = 1; i < rows.length; ++i) {
     var row = rows[i];
     sheet.getRange(i+1, resultColumn).setValue(SlidesApp.openById('1X3tAszOcjo-QkrRNj3TWFm-_--GKPxdLaoJYp6oHJ7M').getUrl());
//    Logger.log(i+1, resultColumn)
 
// Create the text merge (replaceAllText) requests for this presentation (does not make changes yet).
 var requests = [];
for (var colName in name2index) {
  requests.push({
   replaceAllText: {
   containsText: {
   text: '{{' + colName + '}}',
       matchCase: true
    },
   replaceText: row[sheet.getRange(sheet.getLastRow(), name2index[colName])]
  }
});
 }
    
// Execute the requests for this presentation.
var result = Slides.Presentations.batchUpdate({
  requests: requests
 }, '1X3tAszOcjo-QkrRNj3TWFm-_--GKPxdLaoJYp6oHJ7M');

}

Unfortunately it throws NULL for each column.

Any help is appreciated.

Answer 1

Modification points:

• In your question, it seems that you want to use the values of the last row of "Sheet1". But in your script, it seems that you want to use all rows including the last row.
  • From Wish to Achieve : I want to now populate a template Google slide using the last values of each column into placeholders., I believe you wanted to use the values of the last row of "Sheet1".
• When your goal is to use the values of the last row of "Sheet1", and to replace {{A}},{{B}},{{C}},{{D}},{{E}} in the Slides with the values, I think that your for loop is required to be modified. In the current loop, all rows are used and Slides.Presentations.batchUpdate is used in the loop.
• In your situation, I thought that replaceAllText method of Class Slide instead of Slides.Presentations.batchUpdate method of Slides API.

In order to achieve your goal, I would like to propose the following modified script.

Modified script:

function replace_template_with_text_charts() {
  // 1. Retrieve values of header row and last row from Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var [header, ...rows] = sheet.getDataRange().getDisplayValues();
  var lastRow = rows.pop();

  // 2. Put the values of "Generated slide deck" and URL of Slides.
  var resultColumn = sheet.getLastColumn() + 1;
  sheet.getRange(1, resultColumn).setValue("Generated slide deck");
  var slide = SlidesApp.openById('1X3tAszOcjo-QkrRNj3TWFm-_--GKPxdLaoJYp6oHJ7M');
  sheet.getRange(sheet.getLastRow(), resultColumn).setValue(slide.getUrl());

  // 3. Replace `{{A}},{{B}},{{C}},{{D}},{{E}}` with the values of last row of Spreadsheet.
  header.forEach((e, i) => slide.replaceAllText(`{{${e}}}`, lastRow[i]));
}

Note:

• Please use above script with enabling V8 runtime.

Reference:

• replaceAllText(findText, replaceText)