Moving Value from Google Sheets Cell to Google Doc

Question

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:

Timestamp	Name	Email	Criterion1	Criterion2	Agreement1	Agreement2	Response	Number
3/14/2025 10:24:32	Sally	[email protected]	Crit1	Crit2	Yes	Yes	Text I want to move to Google Doc	1
3/14/2025 12:40:57	Joe	[email protected]	Crit1	Crit2	Yes	Yes	More text I want to move to Google Doc	2

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?

Venara · Accepted Answer · 2025-03-27 04:57:16Z

1

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:

edited Mar 27 at 4:57

answered Mar 27 at 4:51

Venara

8312 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user30073632 Mar 27 at 15:22

This is fantastic and worked perfectly! Thank you so much!

Tanaike · Accepted Answer · 2025-03-27 04:48:55Z

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()

Collectives™ on Stack Overflow

Moving Value from Google Sheets Cell to Google Doc

2 Answers 2

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

1 Comment

From:

To:

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

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

1 Comment

From:

To:

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related