CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Max Rowe
Max Rowe

Reputation: 1

"Service Spreadsheets failed while accessing" on (seemingly) simple code

Does anyone have an idea of why my code is throwing the "Service Spreadsheets failed while accessing" error? I've read that this is usually caused by a large dataset, but all the datasets involved here are tiny. For whatever reason, the error is being thrown on line 6. I'm new to both Apps Script and Javascript, but I don't think this code should take very long to run at all. The function simply aims to take values from one sheet and drop them into their corresponding place on another sheet -- I've used formulas in the sheet to find the column number (that is the column_nums var) although ideally I would find an all-script solution. The reason I'm doing this through script and not a simple index match is because I want the values from sheet A to be updated over time and use sheet B to periodically (on a trigger) paste in sheet A's values to track them over time. Apologies if this is a basic question, thanks so much!

function export_maxes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];
  var new_maxes = source.getRange("E1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues();
  var column_nums = source.getRange("A1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues();

  // find row 
  var row_num = destination.getRange("B1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getHeight();
  var row_num = row_num + 1;

  // find columns and input maxes. start at 1 bc data has headers
  for (var i = 1; i < new_maxes.length; i++) {
    destination.getRange(row_num, column_nums[i]).setValue(new_maxes[i])
  } 

};

Upvotes: 0

Views: 179

Answers (1)

Nikko J.
Nikko J.

Reputation: 5543

There is ongoing issue tracker for "getDataRegion failed when it faces hidden rows or columns". Alternative solution is to show the hidden column groups by using method:expandAllColumnGroups() and hide the column after you fetched the data by using method:collapseAllColumnGroups()

Your code should look like this.

function export_maxes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];
  source.expandAllColumnGroups();
  var new_maxes = source.getRange("E1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues();
  var column_nums = source.getRange("A1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues();

  // find row 
  var row_num = destination.getRange("B1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getHeight();
  source.collapseAllColumnGroups();
  var row_num = row_num + 1;

  // find columns and input maxes. start at 1 bc data has headers
  for (var i = 1; i < new_maxes.length; i++) {
    destination.getRange(row_num, column_nums[i]).setValue(new_maxes[i])
  } 
};

References:

expandAllColumnGroups

collapseAllColumnGroups

Upvotes: 1

Related Questions