How to pull values from last row in Google Apps Script

Question

I am trying to create code for Google AppsScript that processes a from on a Google site, dumps the form data into a row on a Google sheet, creates and formats a Google Doc using the data from the spreadsheet. I'm not a programmer yet and I've borrowed different pieces of code to cobble this together. Everything is working except I can't seem to pull the values from the last row within the spreadsheet. It works on the first time through however on submitting form data the second time through the values are still being pulled from row 1. I want the values to be pulled from the last row of data. I've placed my AppScript code below. I've tried many different combinations after reading the Google Apps Script information however I can't make it work.

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

/* @Process Form */
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.company_name,
                formObject.identity_transformation]);
     
}
  
/* This function creates a new document from a template and updates the placeholder with info from a Google Sheet*/
function AutofillDocFromTemplate(){
  // Get the current spreadsheet & sheet
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url).getSheetByName("Data");
  
  
var data = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();  
    
  // Set the range to the last row of data in the Sheet
     
  
  // Get the original template Doc
  const templateDoc = DriveApp.getFileById("1yu5jzg4NbRtTy_UjwzBmnpc-3_pNOqA-l1_UVsiAIWQ");
  // Get the folder for where the docs should go
  const folder = DriveApp.getFolderById("1prOQxp5jmDvJqiwIfLbbkLYWoz5QlTUC");
  // Create the new file name
  const newFileName = ("BrandScript ")
  // Create a copy of the template doc
  const newTempFile = templateDoc.makeCopy(newFileName, folder);
  // Open the new temp doc
  const openDoc = DocumentApp.openById(newTempFile.getId());
  // Get the body of the new temp doc
  const body = openDoc.getBody();
  
  // Replace placeholders with spreadsheet data from last row
  body.replaceText("%company_name%", data[1][0]);
  body.replaceText("%identity_transformation%", data[1][1]);
    
  // Save and close the new doc
  openDoc.saveAndClose();
         
}
  
function AutoDeleteLastRow() {
  var url = "https://docs.google.com/spreadsheets/d/1nz2uIWab1eSirljzvNn6SyNyxz3npDTu4mqVYV0blsU/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  ss.deleteRow(2);
}

Hi. I've tried the solution however it has created new errors farther down in the code where i'm trying to pull the data from the last row into my document. I've spent part of the last two days trying to find a solution for this new problem. Any thoughts on how to fix? — Nate, Nov 05 '20 at 15:13

Marios · Accepted Answer · 2020-11-03T23:51:49.603

Get only the last row - single row:

If you want to get the last row of data with content, then:

replace:

var data = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();

with:

var data = ss.getRange(ss.getLastRow(), 1, 1, ss.getLastColumn()).getValues();

Get all rows but start from the last row - multiple rows:

If you want to pull the whole data range starting from the last row then you can use your current solution but reverse the data array to get the data backwards:

replace:

var data = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();

with:

var data = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues().reverse();

Related:

Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?

How to pull values from last row in Google Apps Script

1 Answers1