1

I am having trouble connecting my spreadsheet data to my google forms in Apps Script.

function createNewGoogleDocs() {
  const googleDocTemplate = DriveApp.getFileById('1cP4N0ueMAG2YNv23epsCpHNHXYZQRV0MqErmOahV9vk');
  const destinationFolder = DriveApp.getFolderById('1bFfhSL5qWnbfiaNrn1qPIRn5fx6odkaN')
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Onboarding')
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[20]) return;
  
    const copy = googleDocTemplate.makeCopy(`${row[2]} ${row[1]} č. ${row[5]} (Experiement)` , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();

        const listArray = ["{{}}","{{Forename}}","{{Middle Name}}","{{Surname}}","{{Business Name}}","{{Business Address}}","{{Post Code}}","{{Business Phone Number}}","{{Email}}","{{Website URL}}","{{Website Platform/Username and Password}}","{{Facebook Ad ID}}","{{Facebook Business ID}}","{{Link to Google Drive}}","{{Additional/Important URLs}}","{{Facebook Login}}","{{Facebook Password}}","{{Instagram Login}}","{{Instagram Password}}"]
 
 
  for (let i in listArray){
      if (row[i] == ""){
        body.findText(listArray[i]).getElement().getParent().removeFromParent();
      } else {
        body.replaceText(listArray[i],row[i]);
      }
    }
    
    doc.saveAndClose();
    
    const url = doc.getUrl();
    sheet.getRange(index + 1, 20).setValue(url)
    
  })
}

Providing me with this response...

TypeError: Cannot read property 'getDataRange' of null createNewGoogleDocs @ Onboarding to Form.gs:7

Rubén
  • 34,714
  • 9
  • 70
  • 166
Shane Woodwards
  • 19
  • 1

1 Answers1

1

Make sure the 'Onboarding' sheet exists. The error you're getting indicates that you're invoking the getDataRange() operation from a null value, in this case, the sheet variable is null.

Pexers
  • 953
  • 1
  • 7
  • 20
  • Thank you for your response, the sheet does exist would there be any other reason for the indication of null? – Shane Woodwards Oct 10 '22 at 19:35
  • I can't see a different reason why `sheet` would be `null`, the code looks alright based on [this documentation](https://developers.google.com/apps-script/reference/spreadsheet/sheet#detailed-documentation). If you found my answer helpful anyway, please consider marking it as 'Accepted', thank you! :) – Pexers Oct 10 '22 at 19:42
  • Try to run the `activate()` function first on the `sheet` variable, before you invoke the `getDataRange()`, it may help. – Pexers Oct 10 '22 at 19:44