CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ysd
ysd

Reputation: 301

calling sheet function in GAS

Is there a way of calling sheet function in google-app-script?

I want to call MATCH(), IMPORTRANGE(), and INDEX() functions to compute the return value in function definition like below.

function abc(foo, bar) {
  a = MATCH(foo, IMPORTRANGE("address of sheet", "A:A"),0)
  b = MATCH(bar, IMPORTRANGE("address of sheet", "B:B"),0)

  return INDEX(IMPORTRANGE("address of sheet", "C:Z"),a,b)
}



Applying the sample provided by @Tanaike

function doGet(e) {
  const [sheet1, sheet2, sheet3] = [e.parameter.url1, e.parameter.url2, e.parameter.url3].map(f => SpreadsheetApp.openByUrl(f).getSheets()[0]);
  const a = sheet1.getRange("A:A").createTextFinder(e.parameter.foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(e.parameter.bar).findNext().getRow();
  const res = sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
  return ContentService.createTextOutput(res);
}

function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit";
  const baseUrl = "https://script.google.com/a/###/macros/s/###/exec";
  
  const res = UrlFetchApp.fetch(`${baseUrl}?url1=${encodeURIComponent(url1)}&url2=${encodeURIComponent(url2)}&url3=${encodeURIComponent(url3)}&foo=${foo}&bar=${bar}`);
  console.log(res.getContentText())
  return res.getContentText();
}

With above which is basically the copy of sample, the abc function returns #VALUE error with length of 50,000 letters.
The console log begins like below.

Logging output too large. Truncating output. 
<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, initial-scale=1" name="viewport">

Upvotes: 0

Views: 464

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this answer?

Modification points:

  • Unfortunately, in the current stage, the built-in functions of Spreadsheet cannot be used with Google Apps Script.
  • When I saw your formula in your pseudo script, I thought that MATCH(foo, IMPORTRANGE("address of sheet", "A:A",0) might be MATCH(foo, IMPORTRANGE("address of sheet", "A:A") ,0).
  • So, in order to convert your formulas to Google Apps Script, it is required to use openByUrl for achieving IMPORTRANGE("address of sheet", "A:A",0). If you want to use this converted script as the custom function, in this case, the custom function cannot use openByUrl due to the permission. So in this case, it is required to run the script using the script editor, the custom menu and the button in Spreadsheet. This is the current specification at Google side. Please be careful this.
    • From your question, I couldn't understand about how do you want to run the script and about address of sheet. So in this answer, I would like to propose to run the script with the script editor.

When above points are reflected to the pseudo script in your question, it becomes as follows. The flow of this sample is as follows.

Flow:

  1. Retrieve sheets.
  2. Retrieve values of a and b.
    • In this case, I used TextFinder.
  3. Retrieve the result value using a and b.

Sample script:

Please copy and paste the following script to the script editor of the Spreadsheet. And, please set the variables of url1, url2 and url3, foo and bar. When you run the script, please run the function of myFunction with the script editor. By this, you can see the retrieved value at the console and also, the value is also put to the active cell as the sample.

function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit"; // Please set this.

  // 1. Retrieve sheets.
  const [sheet1, sheet2, sheet3] = [url1, url2, url3].map(e => SpreadsheetApp.openByUrl(e).getSheets()[0]);  // Modified

  // 2. Retrieve values of `a` and `b`.
  const a = sheet1.getRange("A:A").createTextFinder(foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(bar).findNext().getRow();
  
  // 3. Retrieve the result value using `a` and `b`.
  return sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
}

// Please run this function.
function myFunction() {
  const foo = "###"; // Please set this.
  const bar = "###"; // Please set this.
  const res = abc(foo, bar);
  console.log(res)
  
  SpreadsheetApp.getActiveRange().setValue(res); // Heare, the value is put to the active cell.
}
  • Unfortunately, from your question, I couldn't understand whether each address of sheet is the same URL. So in above sample script, 3 different URLs can be used. Also, the same 3 URLs can be used.
  • const a = sheet1.getRange("A:A").createTextFinder(foo).findNext().getRow(); and const b = sheet2.getRange("B:B").createTextFinder(bar).findNext().getRow(); are a = MATCH(foo, IMPORTRANGE("address of sheet", "A:A",0) and b = MATCH(bar, IMPORTRANGE("address of sheet", "B:B",0), respectively.
    • IMPORTRANGE("address of sheet", "A:A") means "A:A" of the 1st tab in address of sheet.
  • return sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue(); is return INDEX(IMPORTRANGE("address of sheet", "C:Z"),a,b).

Note:

  • When all values of address of sheet in your pseudo script are the active Spreadsheet, abc can be modified as follows. In this case, you can use this function as the custom function like =abc(foo, bar).

      function abc(foo, bar) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    const a = sheet.getRange("A:A").createTextFinder(foo).findNext().getRow();
    const b = sheet.getRange("B:B").createTextFinder(bar).findNext().getRow();
    return sheet.getRange("C:Z").offset(a - 1, b - 1).getValue();
  }

  • Please use this script with enabling V8.

References:

Added:

As other method, in order to use openByUrl in the custom function, I would like to propose to use Web Apps.

Usage:

Please do the following flow.

1. Prepare script.

Sample script of Web Apps is a Google Apps Script. Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps. And please set the URLs. About baseUrl, please set it after the Web Apps is deployed.

function doGet(e) {
  const [sheet1, sheet2, sheet3] = [e.parameter.url1, e.parameter.url2, e.parameter.url3].map(f => SpreadsheetApp.openByUrl(f).getSheets()[0]);
  const a = sheet1.getRange("A:A").createTextFinder(e.parameter.foo).findNext().getRow();
  const b = sheet2.getRange("B:B").createTextFinder(e.parameter.bar).findNext().getRow();
  const res = sheet3.getRange("C:Z").offset(a - 1, b - 1).getValue();
  return ContentService.createTextOutput(res);
}

function abc(foo, bar) {
  const url1 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url2 = "https://docs.google.com/spreadsheets/d/###/edit";
  const url3 = "https://docs.google.com/spreadsheets/d/###/edit";
  const baseUrl = "https://script.google.com/macros/s/###/exec";
  
  const res = UrlFetchApp.fetch(`${baseUrl}?url1=${encodeURIComponent(url1)}&url2=${encodeURIComponent(url2)}&url3=${encodeURIComponent(url3)}&foo=${foo}&bar=${bar}`);
  return res.getContentText();
}

2. Deploy Web Apps.

  1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
  2. Select "Me" for "Execute the app as:".
    • By this, the script is run as the owner.
  3. Select "Anyone, even anonymous" for "Who has access to the app:".
  • In this case, no access token is required to be request. I think that I recommend this setting for testing this workaround.
  • Of course, you can also use the access token. When you use the access token, please include one of scopes for Drive API like https://www.googleapis.com/auth/drive.readonly.
  • And also, I think that a key value can be used as the query parameter instead of the access token.
  1. Click "Deploy" button as new "Project version".
  2. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  3. Click "OK".
  4. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

3. Testing Web Apps using custom function.

Please set the URL of your Web Apps to baseUrl of the above Google Apps Script and redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps.

And, as the test of this script, please put =abc(foo, bar) to a cell. By this, the function abc is run and the result value is retrieved using UrlFetchApp.

Note:

  • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this.

References:

Upvotes: 3

Related Questions