0

I have found other posts with similar issues: Opening file throws error but still works Google API error, but still works but unfortunately, I am so new to Google Sheets/GAS that I just can't take it from there. I'm hoping someone can assist with this error, and tell me how to change it? (I was initially hired to complete a Smartsheet project, but the client didn't actually have a Smartsheet project, she was just calling it that lol. It was actually a Google Sheets project, so I'm learning on the fly as best as I can, but I can't even format this code below correctly - ugh!)
The error I am receiving is this:

TypeError: Cannot read property 'changeType' of undefined
onChange    @ SortByColorScript.gs:2

And the code is below:

function onChange(e){
if(e.changeType == 'EDIT' || e.changeType == 'OTHER'){
    
// your code here
const sheetName = "Patient Progress"; // Please set the sheet name.

// 1. Retrieve the background colors from the cells.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const range = sheet.getRange(3, 1, ss.getLastRow() - 1, ss.getLastColumn());
const backgrounds = range.getBackgroundObjects();

// 2. Create the request body for using the batchUpdate method of Sheets API.
// This means "red","orange","yellow","green","blue","purple","white" in that order.
const sortColorOrder = ['#ea9999', '#f9cb9c', '#fff2cc', '#d9ead3', '#cfe2f3', '#d9d2e9'];   
const backgroundColorObj = backgrounds.reduce((o, [a]) => {
  const rgb = a.asRgbColor();
  const hex = rgb.asHexString();
  return Object.assign(o, { [hex == "#ffffffff" ? "#ffffff" : hex]: { red: rgb.getRed() 
/ 255, green: rgb.getGreen() / 255, blue: rgb.getBlue() / 255 } });
}, {});
const backgroundColors = sortColorOrder.map(e => backgroundColorObj[e] || 
"").filter(String);
if (backgroundColors.length == 0) return;

const startRow = range.getRow() - 1;
const startColumn = range.getColumn() - 1;
const srange = {
  sheetId: sheet.getSheetId(),
  startRowIndex: startRow,
  endRowIndex: startRow + range.getNumRows(),
  startColumnIndex: startColumn,
  endColumnIndex: startColumn + range.getNumColumns()
};
const requests = [
  { sortRange: { range: srange, sortSpecs: [{ dimensionIndex: 0, sortOrder: "ASCENDING" 
}] } },
  { sortRange: { range: srange, sortSpecs: backgroundColors.map(rgb => ({ 
backgroundColor: rgb })) } }
];

// 3. Request to Sheets API using the request body.
Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
}
else {
  return;
}
}

Again, this IS working, but I'd like to not have any errors. I did enable the Sheets API. I'm so sorry about this formatting. No matter how I do it, it still looks wrong. I never had these issues on the C++ forum, not sure what my issue is, but I'm sorry.

Erin
  • 3
  • 3
  • Did you check how many trigger fired when function called? It seems does not catch e at the first place. Maybe other function did the work. – liquidkat Apr 07 '22 at 23:14
  • I don't know how to check that. But I don't have any other functions, just this one. – Erin Apr 07 '22 at 23:16
  • I found the Executions page though, and it's telling me I don't have an active sheet selected? How can that be since changing a cell is what calls it...? I have two sheets in my project, same workbook (or whatever a 'wkbk' is called in GS.) The first sheet is editable, and the second sheet simply copies the lines from sheet 1 and sorts them by background cell color. So it must be when I'm on Sheet1 instead of sheet 2 (Patient Progress). I just have no idea how to modify this code - can I create an onChange function on Sheet 1 that calls the sheet 2 sort? Or what is recommended? – Erin Apr 07 '22 at 23:27
  • Are you attempt to run this function from the script editor? – Cooper Apr 08 '22 at 00:06
  • 1
    How do you want to run your script of `onChange`? Because, from your error message, I thought that you might have directly run `onChange` instead of the OnChange trigger. So, first, I thought that I would like to understand your goal. For example, when you installed your function as the OnChange trigger, please edit the sheet. By this, your function is run. Is this your expected result? – Tanaike Apr 08 '22 at 00:07
  • I have done both, I'm sorry for the confusion. My goal is simply that when sheet 1 is edited, sheet2 runs the script. Which does work. Is the script erroring out when I run it on the Script page, because the Script page is the active page? Omgoodness, you're kidding me? So I've been looking all day for an error that didn't even exist? I really need to start at the beginning with this training and not take any more Google Sheets projects, even by accident. – Erin Apr 08 '22 at 00:19
  • You cannot run this script without supplying the event object which comes from the onChange trigger. – Cooper Apr 08 '22 at 00:25
  • Thank you for replying. From `My goal is simply that when sheet 1 is edited, sheet2 runs the script.`, I proposed a modified script in an answer. Could you please confirm it? But, if I misunderstood your goal and that was not useful, I apologize. – Tanaike Apr 08 '22 at 00:56

1 Answers1

0

From My goal is simply that when sheet 1 is edited, sheet2 runs the script., if you want to run the script using sheet 2 when sheet 1 is edited, how about modifying it as follows?

Modified script:

Please copy and paste the following script to the script editor. And please copy and paste your script below the line of // 1. Retrieve the background colors from the cells.. And, please install the OnEdit trigger to the function of installedTriggerFunction. Ref

When you use this script, please edit a cell of sheet 1. By this, the script is run.

function installedTriggerFunction(e) {
  const sheet1Name = "Sheet1"; // Please set your sheet name of sheet 1.
  const sheet2Name = "Sheet2"; // Please set your sheet name of sheet 2.

  const activeSheetName = e.source.getActiveSheet().getSheetName();
  if (activeSheetName != sheet1Name) return;

  // your code here
  const sheetName = sheet2Name; // Please set the sheet name.

  // Below is your script.

}

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165