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 | Criterion1 | Criterion2 | Agreement1 | Agreement2 | Response | Number | |
---|---|---|---|---|---|---|---|---|
3/14/2025 10:24:32 | Sally | Sally@gmail.com | Crit1 | Crit2 | Yes | Yes | Text I want to move to Google Doc | 1 |
3/14/2025 12:40:57 | Joe | Joe@gmail.com | 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?
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: