How to deal with merged cells with GAS?? and how could I fix the error?-CodePudding

I am a very beginner, so I am sorry for asking a low-leveled question. I want to drag out values on each merged cell (8 cells for each and listed down on a spreadsheet), and want to put them in a pull-down of google form. I just tried to see values on the sheet but found out there was an error. If someone know how to fix this, I am glad to hear that.

Error
TypeError: Cannot read property 'getRange' of undefined updateFormList @ Code.gs:13

function updateFormList() {
  var formId  = '---'
  var sheetId = '---'
  var sheetName = 'Sheet1'
  
  var range = sheet.getRange("A3:A10");
  var mergedRanges = range.getMergedRanges();
    for (var i = 0; i < mergedRanges.length; i  ) {
  Logger.log(mergedRanges[i].getA1Notation());
  Logger.log(mergedRanges[i].getDisplayValue());
  }
}

CodePudding user response：

Try this:

function updateFormList() {
  var formId  = '---'
  var sheetId = '---'
  var sheetName = 'Sheet1'
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange("A3:A10");
  var mergedRanges = range.getMergedRanges();
    for (var i = 0; i < mergedRanges.length; i  ) {
  Logger.log(mergedRanges[i].getA1Notation());
  Logger.log(mergedRanges[i].getDisplayValue());
  }
}

CodePudding user response：

To fix the error you have to assign a Class Sheet object to the variable named sheet.

function updateFormList() {
  var formId  = '---'
  var sheetId = '---'
  var sheetName = 'Sheet1'
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // this works in scripts bounded to spreadsheet
  var sheet = spreadsheet.getSheetByName(sheetName);

  var range = sheet.getRange("A3:A10");
  var mergedRanges = range.getMergedRanges();
    for (var i = 0; i < mergedRanges.length; i  ) {
      Logger.log(mergedRanges[i].getA1Notation());
      Logger.log(mergedRanges[i].getDisplayValue());
  }
}

Regarding

I want to drag out values on each merged cell (8 cells for each and listed down on a spreadsheet), and want to put them in a pull-down of google form.

Merged cells might be helpful for data visualization, i.e. creating a report, but they might need more complex scripts and formulas. As a "very beginner" and when programming efficiency be important, whenever you can avoid having merged cells in your spreadsheets, specially if you will have to use formulas and scripts on their content.

Resources

https://developers.google.com/apps-script/guides/sheet