2

Google Sheets has a new feature rolled out earlier called named functions and it is pretty useful. However, you need to manually import it into your sheet. I didn't see anything in the docs on how to do it programmatically. Do anybody know how to do that?

Thought someone might know because there are some functions that are hidden like programmatic enabling a link to another sheet via importrange.

This is what I came up with in hoping someone else with similar problem can benefit.

I had this as a named function =date(left(text,4),mid(text,5,2),right(text,2)) all it did was take a string such as 20221027 and turns it into a date format such as 10/27/2022.

It's easier to not use the named function and just use the new built in map and lambda

map(A1, lambda(text, date(left(text,4),mid(text,5,2),right(text,2))))

then you can replicate that across your sheets with the API.

A1 itself is a formula, without map, lambda, the formula is 3x longer and really difficult to read and edit.

jason
  • 3,811
  • 18
  • 92
  • 147
  • Regarding to the last statement, it looks that you are referring to a solution, AFAIK, originally shared on [Franzi's](https://stackoverflow.com/users/1916298/franzi) [answer](https://stackoverflow.com/a/67280686/1595451) to [How to allow access for importrange function](https://stackoverflow.com/q/28038768/1595451) "I'm doing it automatically using the [gspread](https://gspread.readthedocs.io/) python library by calling the **addimportrangepermissions** endpoint". The site of the referred Python library point people having questions about that library to this site using [tag:gspread]. – Rubén Dec 10 '22 at 23:39
  • Once said that, I don't think that the question as is currently wrote is a good fit for this site. Please [edit] the question to show what you have tried to find the undocumented API endpoint / request. – Rubén Dec 10 '22 at 23:44
  • 1
    Per @Rubén's suggestion, converted to comment. The [Google Apps Script reference](https://developers.google.com/apps-script/reference) doesn't seem to mention named functions, so I don't believe named functions are supported yet in Apps Script. Interestingly enough, [Tanaike](https://stackoverflow.com/users/7108653/tanaike) [wrote a script](https://gist.github.com/tanaikech/9a9e571ed662e35eec0aa747bb4e025a) to actually download a spreadsheet as an Excel file and then scrape named function data from it, but it only reads the function names -- it doesn't actually import them into another sheet. – great_pan Dec 10 '22 at 23:59
  • @Rubén Can you clarify why the original post wasn't appropriate as an answer and needed to be converted to a comment? The original user asked a question, and my reply provided a good-faith effort at answering the question -- I wasn't asking for additional clarification or expanding the conversation. – great_pan Dec 11 '22 at 00:01
  • @great_pan I think that this request asking clarification for a comment that is now deleted due to the deletion of the referred answer, should had been posted on the answer. Anyway, please bear in mind that not all questions should be answered. The intention of this site is to be strict regarding the post content, questions should be questions and answers should answer the corresponding question, without any "chit-chat" (ref. [ask], [answer], https://stackoverflow.com/help/behavior), contrary as happens with other sites usually referred as "forums". – Rubén Dec 11 '22 at 00:13
  • Please note the use of "site intention". You might already have found or you will do at some point posts that someway divert from the "site intention" as I undertand it. This occurs because might have a different understanding, others just don't care and there are not enough people reviewing posts and many of who are doing that are not very strict. – Rubén Dec 11 '22 at 00:24
  • 1
    I think the closest things we have is [custom functions](https://developers.google.com/apps-script/guides/sheets/functions) – Cooper Dec 11 '22 at 00:55
  • 1
    Please clarify what you are trying to accomplish by copying named functions from one spreadsheet to another. Add code that shows the issue you have, perhaps basing the code on [Tanaike's gist](https://gist.github.com/tanaikech/9a9e571ed662e35eec0aa747bb4e025a). – doubleunary Dec 11 '22 at 07:38
  • It's unclear what is the purpose of the content added in the last review, if it is a workaround (apparently it's becuase it propose some new funcitons instead of a named function) it should be posted as an answer, if it's an attemp to clarify the question it's unclear what is the current problem / issue to be solved. – Rubén Dec 11 '22 at 16:47
  • First, I apologize that [my sample script](https://gist.github.com/tanaikech/9a9e571ed662e35eec0aa747bb4e025a) was not useful for your situation. In the current stage, unfortunately, the named functions cannot be directly managed by Google Spreadsheet service (SpreadsheetApp) and Sheets API. (I believe that this will be resolved in the future update.) So, as a current workaround, I proposed a sample script for retrieving the named functions from Spreadsheet. – Tanaike Dec 12 '22 at 04:05
  • Although I have already mentioned in my post, of course, when this workaround is used, the named functions can be put into the Spreadsheet. But, in this method, it is required to overwrite Google Spreadsheet with XLSX data, and in the current stage, Google Spreadsheet is not completely the same as XLSX data. From this situation, I have not proposed a sample script for putting the named functions into Google Spreadsheet. I apologize for this. – Tanaike Dec 12 '22 at 04:05
  • For example, if your spreadsheet is a new spreadsheet or only the simple values are included, I think that this method will be able to be used. So, if you want a sample script, first, I thought that it is required to confirm your current Spreadsheet. – Tanaike Dec 12 '22 at 04:08

1 Answers1

0

From : https://support.google.com/docs/answer/12504534?hl=en

Import named functions As you create more named functions or start to find useful ones that others created, you want to be able to use them in different sheets. To reuse created named functions, you can import them from another sheet into your current one:

On your computer, open Google Sheets. Open the sheet that you want to import the named functions to. At the top, click Data and then Named functions and then Import function. Select the sheet that you want to import from. Select the named functions that you want to import. If you want to import all the named functions from the file, click Import all. Click Import.

Ajeet Ganga
  • 8,353
  • 10
  • 56
  • 79