google-apps-script - How do i deal with getRange() Class error in Google Script?

Question

I am working in Google Script and trying to get values from following column. Any idea of why i am getting error "TypeError: Cannot read property 'getRange' of undefined", and how to deal with it? Here is following code:

const ss = SpreadsheetApp.openById(ssId);
const workspaceSheet = ss.getSheetByName("Workspaces")
function getWorkspaces (workspaceSheet) {
  WORKSPACE_ID = workspaceSheet.getRange(1,1,100).getValues()
  return WORKSPACE_ID
}

标签： google-apps-scriptgoogle-sheets

Answer 1

When getWorkspaces is run as the custom function, an error occurs at const ss = SpreadsheetApp.openById(ssId);. From this situation, I thought that you might have directly run the function of getWorkspaces with the script editor. If my understanding is correct, when getWorkspaces is run with the script editor, workspaceSheet of getWorkspaces(workspaceSheet) is undefined. By this, such error occurs. If you want to remove your issue, how about the following modification?

Modified script 1:

In this modification, workspaceSheet of getWorkspaces(workspaceSheet) is removed. By this, workspaceSheet of const workspaceSheet = ss.getSheetByName("Workspaces"); is used.

const ssId = "###"; // Please set the Spreadsheet ID.
const ss = SpreadsheetApp.openById(ssId);
const workspaceSheet = ss.getSheetByName("Workspaces");

function getWorkspaces() {
  WORKSPACE_ID = workspaceSheet.getRange(1, 1, 100).getValues();
  return WORKSPACE_ID;
}

Modified script 2:

In this modification, ss and workspaceSheet are declared in the function.

function getWorkspaces() {
  const ssId = "###"; // Please set the Spreadsheet ID.
  const ss = SpreadsheetApp.openById(ssId);
  const workspaceSheet = ss.getSheetByName("Workspaces");
  WORKSPACE_ID = workspaceSheet.getRange(1, 1, 100).getValues();
  return WORKSPACE_ID;
}

Modified script 3:

When you want to call getWorkspaces from other function, how about the following modified script?

function getWorkspaces(workspaceSheet) {
  WORKSPACE_ID = workspaceSheet.getRange(1, 1, 100).getValues();
  return WORKSPACE_ID;
}

// In this case, please run this function. By this, `getWorkspaces` is run.
function sample() {
  const ssId = "###"; // Please set the Spreadsheet ID.
  const ss = SpreadsheetApp.openById(ssId);
  const workspaceSheet = ss.getSheetByName("Workspaces");
  const WORKSPACE_ID = getWorkspaces(workspaceSheet);
  console.log(WORKSPACE_ID);
}