Retrieving Named Functions from Google Spreadsheet using Google Apps Script

submit.md

Retrieving Named Functions from Google Spreadsheet using Google Apps Script

This is a sample script for retrieving the named functions from Google Spreadsheet using Google Apps Script.

Recently, the named functions got to be able to be used in Google Spreadsheet. Ref When several named functions are added, I thought that I wanted to retrieve these functions using a script. But, unfortunately, in the current stage, it seems that there are no built-in methods (SpreadsheetApp and Sheets API) for directly retrieving the named functions. So, I created this sample script.

In this script, the following flow is run.

1. Convert Google Spreadsheet to XLSX format.
2. Retrieve the data from XLSX data.
3. Parse XLSX data and retrieve the named functions.

Sample script

function myFunction() {
  const spreadsheetId = "###"; // Please set Spreadsheet ID.

  // Convert Google Spreadsheet to XLSX format.
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=${ss.getId()}`;
  const resHttp = UrlFetchApp.fetch(url, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  });

  // Retrieve the data from XLSX data.
  const blobs = Utilities.unzip(resHttp.getBlob().setContentType(MimeType.ZIP));
  const workbook = blobs.find((b) => b.getName() == "xl/workbook.xml");
  if (!workbook) {
    throw new Error("No file.");
  }

  // Parse XLSX data and retrieve the named functions.
  const root = XmlService.parse(workbook.getDataAsString()).getRootElement();
  const definedNames = root
    .getChild("definedNames", root.getNamespace())
    .getChildren();
  const res = definedNames.map((e) => ({
    definedName: e.getAttribute("name").getValue(),
    definedFunction: e.getValue(),
  }));
  console.log(res);

  // DriveApp.getFiles(); // This comment line is used for automatically detecting the scope of Drive API.
}

• If you want to use the active Spreadsheet, please modify const ss = SpreadsheetApp.openById(spreadsheetId) to const ss = SpreadsheetApp.getActiveSpreadsheet().

• When this script is run, all named functions are obtained from the Spreadsheet.

Testing

When this script is run to the top sample situation, the following result is obtained.

[
  {
    "definedName": "CONTAINS",
    "definedFunction": "LAMBDA(cell, range, NOT(ISERROR(MATCH(cell,range,0))))"
  },
  { "definedName": "SAMPLE1", "definedFunction": "LAMBDA(range, SUM(range))" }
]

• Unfortunately, in the current stage, the description of the named function cannot be obtained.

• At XLSX format, the named functions are used as LAMBDA function. If you want to directly use this LAMBDA function, for example, please put a function like =LAMBDA(range, SUM(range))(A1:A5) into a cell. By this, the LAMBDA function can be run. Of course, you can retrieve the function from this result and put it as the named function again.

Note

• If an error is related to the scopes, please enable Drive API at Advanced Google services and test it again.

• When this method is used, the named functions can be added and copied from Spreadsheet A to Spreadsheet B. But, in this method, Google Spreadsheet is converted to XLSX format. When a new named function is added, Google Spreadsheet is required to be updated by XLSX data. Unfortunately, XLSX data is not the completely same as Google Spreadsheet. So, in this post, I didn't add this sample script for creating new named functions.

• I believe that the named functions can be retrieved by the built-in methods of SpreadsheetApp and Sheets API by the future update on the Google side.

Thanks a lot for the insight and example.
Here is my solution for C#
https://gist.github.com/Rovsau/88654e9714d5818288faca43b7c95b32

Is there any similar workaroud for the other way around? i.e., programmatically creating or updating named functions

Is there any similar workaroud for the other way around? i.e., programmatically creating or updating named functions

Yes, I am looking for this too.

Thank you for this. I have a google sheet with ~1,000 named functions (I think it was created from Excel using Capital IQ Plugin).
I need to delete those functions.
It takes me about 31 seconds per function to do using the UI.

As far as I can tell, there's no way to delete these with a script.

@BenHizak
I think your comment is correct. Unfortunately, at this stage, Google doesn't provide methods for managing named functions. So, if you want to remove many named functions with a low processing cost, you'll need to use a workaround.

Since I'm not sure of your exact situation, please consider this one of several possible approaches. The workaround is as follows:

1. Create a new Google Spreadsheet.
2. Copy all values from your current spreadsheet to the new one.
3. If you use font colors, cell colors, data validations, and so on, you can copy those as well.

This process creates a spreadsheet that doesn't include the named functions. But I apologiz