CODEWITHSUNDEEP
javascriptgoogle-apps-script
xyz
xyz

Reputation: 2300

Add dummy parameter to Custom function for Google sheets

I have the custom function below; it moves columns from one sheet to a different sheet in Google Sheets

I got it here Non-contiguous column copy from one spreadsheet to another in google apps

I need to add a dummy parameter to it so I can get it to refresh the data it pulls

I tried simply doing this: function copyColumns(sourceRange,start,sheetKey, dummy) (which has worked for other custom functions)

but I keep getting an error:

Problem getting sheet1 - Exception: You do not have permission to perform that action. (line 31).

Which is: throw "Problem getting sheet" + sheetKey + " - " + err;

I know some VBA but am new to Google script writing, I have tried but have not worked out how to do this

Thanks

/* =copyColumns("MyDataSheet!C,A,W",8) */

function copyColumns(sourceRange,start,sheetKey) {
  // Initialize optional parameter
  if(!sheetKey && typeof start!== "number") {
    sheetKey = start;
    start = 1;
  } else {
    start = start || 1;
  }
  // Check SourceRange Input
  var inputRe = /^((.*?!)(?=[a-z],?|[a-i][a-z]))?[a-i]?[a-z](,[a-i]?[a-z])*$/i;
  if(!inputRe.test(sourceRange))
    throw "Invalid SourceRange: " + sourceRange;

  // Check Start Row
  if(typeof start !== "number")
    throw "Starting row must be a number! Got: " + start;
  if(start % 1 !== 0)
    throw "Starting row must be an integer! Got: " + start;
  if(start < 1)
    throw "Starting row can't be less than 1! Got: " + start;

  // Get the Source Sheet
  try {
    var ss = sheetKey
           ? SpreadsheetApp.openById(sheetKey)
           : SpreadsheetApp.getActiveSpreadsheet();
  }  catch(err) {
    throw "Problem getting sheet" + sheetKey + " - " + err;
  } 
  var sheetName = sourceRange.match(/^.*?(?=!)/);
  var sheet = sheetName
            ? ss.getSheetByName(sheetName[0])
            : ss.getActiveSheet();

  // Check that everything is still valid
  if(!sheet)
    throw "Could not find sheet with name: " + sheetName;
  if(start > sheet.getLastRow())
    throw "No data beyond row: " + start + " Last row: " + sheet.getLastRow();

  // Get the values
  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.getLastRow()-start+1;
  var values = sheet.getRange(start,1,lastRow,lastCol).getValues();

  // Get the desired columns from the string
  var desiredColMatch = sourceRange.match(/([a-i]?[a-z](,[a-i]?[a-z])*)$/i);
  var desiredColumns = desiredColMatch[0].toUpperCase().split(",");

  // In case the column we are trying to grab doesn't exist in the sheet
  var lastColId = sheet.getMaxColumns() - 1; // Array is 0 indexed, Sheet is 1

  // Get the numerical values of the passed in Column Ids
    var columns = desiredColumns.map(function(colId){
    var num = colId.length - 1; // 0 or 1
    var colNum = colId.charCodeAt(num)-65+num*26*(colId.charCodeAt(0)-64);
    if(colNum > lastColId)
      throw "Invalid Column: " + colId + " - Column not in: " + sheetName;
    return colNum;
  });

  //Map the values to a new array of just the columns we want
  return values.map(function(row){
    return columns.map(function(col){
      return row[col]
    })
  });
}

Upvotes: 0

Views: 746

Answers (1)

Wicket
Wicket

Reputation: 38305

The error isn't related to the use of a dummy parameter. The error occurs because custom functions can't call services that requires authorization to run. Specifically, the Spreadsheet service (SpreadsheetApp) requires authorization to be ran.

From https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, ...

Upvotes: 1

Related Questions