How to Transfer Text Data from Spreadsheet Cells to Document Pages

Other Questions Google Sheets
1

I’m working with a spreadsheet that contains lengthy text entries in column H, which come from form submissions. My goal is to create a document where each text response appears on its own page along with its corresponding ID number from column I.

Current Setup

Date User Contact Field1 Field2 Consent1 Consent2 Feedback ID
3/14/2025 09:15:22 Alice [email protected] Option1 Option2 Agreed Agreed Sample feedback text for document 1
3/14/2025 11:30:45 Bob [email protected] Option1 Option2 Agreed Agreed Additional feedback for second page 2

Expected Output

Page 1:
1
Sample feedback text for document

Page 2:
2
Additional feedback for second page

My Current Code

function transferData() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const worksheet = workbook.getActiveSheet();
  const totalRows = worksheet.getLastRow();

  const newDoc = DocumentApp.create("Feedback Responses Collection");
  const docBody = newDoc.getBody();
  
  var totalCols = worksheet.getLastColumn();
  var allData = worksheet.getSheetValues(1, 1, totalRows, totalCols);

  for(let i = 0; i < totalRows; i++){
      var itemNumber = i + 1;
      var feedbackText = allData[itemNumber][7];

      docBody.appendParagraph(itemNumber);
      docBody.appendParagraph(feedbackText);
      docBody.appendPageBreak();
  }
}

Error Message

TypeError: Cannot read properties of undefined (reading '7')
transferData @ Code.gs:21

I’ve tried different approaches like using index 8, hardcoding values, adjusting the loop to start from 1, but nothing works. Any ideas what might be causing this issue?

2

Your loop logic has an off-by-one error. You start at i = 0 but then set itemNumber = i + 1 to access the array. When i = 0, itemNumber = 1, so you’re hitting allData[1] (the second row) instead of the first data row.

Since you’re using getSheetValues(1, 1, totalRows, totalCols) with the header, allData[0] is your header and allData[totalRows] doesn’t exist. I hit the same issue pulling survey data last year.

Two quick fixes: either start your loop at i = 1 and use allData[i][7] directly, or keep i = 0 but drop the itemNumber variable and just use allData[i][7]. You could also skip the header completely by starting getSheetValues at row 2 if you don’t need it.

3

You’re getting undefined because the array index is out of bounds. Change var itemNumber = i + 1; to var itemNumber = i; and use allData[i][7] instead. Also start your loop from i=1 to skip the headers. Had the same issue with my timesheet app.

4

Your problem is array indexing mixed with wrong loop bounds. You’re getting totalRows including the header, but your loop goes from 0 to totalRows-1. Then you do itemNumber = i + 1 and use allData[itemNumber][7] - so you’re trying to access rows 1 through totalRows, but your array only goes from 0 to totalRows-1. I hit this same bug building a survey feedback processor. Fix: start your loop at row 1 to skip headers and run until totalRows-1. Change it to for(let i = 1; i < totalRows; i++) and just use allData[i][7] and allData[i][8] directly for feedback text and ID. This way, you’ll access the correct data rows without going out of bounds.