google-sheetsgoogle-apps-scriptgoogle-sheets-apigoogle-docsgoogle-docs-api

Moving Value from Google Sheets Cell to Google Doc


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 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?


Solution

  • 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:

    Application - Docs file