I have a Google Sheet that has long blocks of text in each cell of column H (it is input from a long-form response box in a Google Form). I want to take each response and move it onto a new page of a Google Doc, with the response number (column I).

The spreadsheet looks something like this:

I have a Google Sheet that has long blocks of text in each cell of column H (it is input from a long-form response box in a Google Form). I want to take each response and move it onto a new page of a Google Doc, with the response number (column I).

The spreadsheet looks something like this:

Ideally, the Google Doc would then have:

Page 1:

1

Text I want to move to Google Doc

Page 2:

2

More text I want to move to Google Doc

I wrote the following short script, which I thought would do this:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const rowCount = sheet.getLastRow();


  const doc = DocumentApp.create("Application (Responses) (1)");
  const body = doc.getBody();
  
  var numColumns = sheet.getLastColumn();

  //var data = sheet.getDataRange().getValues();

  var data = sheet.getSheetValues(1, 1, rowCount, numColumns);
  //Logger.log(data);

  

  for(let counter = 0; counter < rowCount; counter = counter+1){
      var number = counter+1;
      var response = data[number][7];

      body.appendParagraph(number);
      body.appendParagraph(response);
      body.appendPageBreak();
  }
}

However, I continually get the following error:

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

I have tried using 8 instead of 7, hardcoding in a value, using numColumns-1, using a variety of different ways to initialize data, changing the for loop to run from 1 to <= rowCount, etc. - nothing seems to be working.

Does anyone have a suggestion for how to solve this?

• google-sheets
• google-apps-script
• google-sheets-api
• google-docs
• google-docs-api

2 Answers 2

Fixing Row Indexing to Move Values from Google Sheet to Google Docs

The error you encountered TypeError: Cannot read properties of undefined (reading '7') was due to starting the row indexing from the header, which caused misalignment with the data.

I adjusted the code to skip the header row and start fetching data from row 2.

const data = sheet.getSheetValues(2, 1, rowCount - 1, sheet.getLastColumn());

I also fixed the loop to correctly go through the rows without going out of bounds. Instead of using counter + 1, which caused index errors, I used the counter directly to access data[counter].

  for (let counter = 0; counter < rowCount - 1; counter++) { 
      var number = data[counter][8]; 
      var response = data[counter][7]; 

Here is the Complete Code:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const rowCount = sheet.getLastRow();

  const doc = DocumentApp.create("Application (Responses) (1)");
  const body = doc.getBody();
  const data = sheet.getSheetValues(2, 1, rowCount - 1, sheet.getLastColumn());

  for (let counter = 0; counter < rowCount - 1; counter++) {
    var number = data[counter][8];
    var response = data[counter][7];

    body.appendParagraph(number.toString());
    body.appendParagraph(response);
    body.appendPageBreak();
  }
}

Sample Output:

In your showing table, 3 rows, including the 1st header row, are existing. But, in your script, at for(let counter = 0; counter < rowCount; counter = counter+1){, number becomes 4 at the last element by var number = counter+1;, although the length of the array data is 3. I think that this is the reason for your current issue of TypeError: Cannot read properties of undefined (reading '7'). In order to avoid this, how about the following modification?

From:

for(let counter = 0; counter < rowCount; counter = counter+1){
    var number = counter+1;
    var response = data[number][7];

    body.appendParagraph(number);
    body.appendParagraph(response);
    body.appendPageBreak();
}

To:

data.forEach((row, number) => {
  if (number == 0) return;
  var response = row[7];
  body.appendParagraph(number);
  body.appendParagraph(response);
  body.appendPageBreak();
});

• By this modification, the data rows are used without the 1st header row.

Reference:

• forEach()