User Defined Function issue with Sheets App Script

[ryan rogers]

To whomever reads this, hello.

I'm having a bit of an issue here that I can't seem to figure out.

I created a script for a google sheet a while ago called, getSheetUrl(). All it does is allow me to download a single tab instead of the whole sheet. Recently, I was tasked with importing some of the data to another google sheet, so I thought it would be nice to automate that. 

So I made a second function (WriteGaffers()) to go through the current sheet and write to an array if certain values are there. That array then gets passed to the second sheet and writes to it at the bottom of the sheet. It all works pretty nicely.

Then I tried to combine the 2 functions. I can't call the second function from within the first. Then I tried to make a third function (COMBOPLATTER()) to call both of them. Again the second function won't run. 

I'm not sure what the problem is. Is it because WriteGaffers() is "Editor" and not "Custom Function"? If so how can I change that? Ideas welcomed. 

[Keith Andersen]

Without seeing the functions - no way to help.

Can you share a mock sheet with the functions so we can test/troubleshoot? Just posting the script won't really help.

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5b6e969f-502a-45cf-ac0d-3065ed566493n%40googlegroups.com.

[ryan rogers]

Thanks for taking the time to respond. I have a couple of links to sample sheets provided. I'm trying to fire off the code from the "DOWNLOAD"  hyperlink I made in I210 on the ABP Sample sheet. 

https://docs.google.com/spreadsheets/d/1MXQiT8U9jOwr6uAAVo4dVj7vxHolaenAY7QBsQnqnrw/edit?usp=sharing

Then if column AJ is > 0 and the row is odd, it gets the needed info into an array and appends it on to the end of this sheet. 

https://docs.google.com/spreadsheets/d/1ZZz-eUGInbPdZJ78SqUjHulEO8umi5bnFcRCo5sJceY/edit?usp=sharing

Here is the code incase I didn't save it properly in the project. 

function getSheetUrl() {

  var SS = SpreadsheetApp.getActiveSpreadsheet();

  var ss = SS.getActiveSheet();

  var url = '';

  url += SS.getUrl();

  url = url.substring(0, url.length - 4);

  url += 'export?format=xlsx&gid=';

  url += SS.getSheetId();

  return url;

}

function WriteGaffers() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

  var name = sheet.getName()

  var lastRow = sheet.getLastRow()

  var qty = ""

  var containerNum = ""

  var shipDate = new Date(Date.now())

  let valuesToWrite = [[]];

  lastRow = lastRow - 2

  for (var i = 6; i<lastRow; i+=2){

    qty = sheet.getRange(i, 47).getValue();

    if (qty>0 && i%2==0){

      var iDate = i + 1

      shipDate = sheet.getRange(iDate, 2).getValue()

      shipDate = ((shipDate.getUTCMonth() +1)+"/"+shipDate.getUTCDate()+"/"+shipDate.getUTCFullYear())

      loadLineNum = sheet.getRange(i, 5).getValue()

      containerNum = sheet.getRange(i,8).getValue()

      containerNum = containerNum.replace("\n","")

      if(valuesToWrite[0][0] == null){

        valuesToWrite[0][0] = shipDate

        valuesToWrite[0][1] = qty

        valuesToWrite[0][2] = loadLineNum

        valuesToWrite[0][3] = containerNum

      }else{

        valuesToWrite.push([shipDate, qty, loadLineNum, containerNum])

      }

    }  

  }

  //Now that it's done getting values, lets make sure its not null before passing that array to the other sheet.

  if (valuesToWrite[0][0] != null){

//Normally you can use sheet.getLastRow(), but this sheet has 1000 check boxes and so the last row would always

//be 1000. The bottom 2 lines of code get all of the values in column A, filter them as string, and counts the

//length.

    const returnSheet = SpreadsheetApp.openById('1ZZz-eUGInbPdZJ78SqUjHulEO8umi5bnFcRCo5sJceY')

      .getSheetByName('GAFFERS');

    returnVals = returnSheet.getRange("A1:A").getValues()

    returnLastRow = returnVals.filter(String).length;

    returnSheet.getRange(returnLastRow + 1, 1,valuesToWrite.length,valuesToWrite[0].length)

      .setValues(valuesToWrite);

  }

  //return "";

}

function COMBOPLATTER(){

  //nonsense = WriteGaffers();

  WriteGaffers();

  combo_url = getSheetUrl();

  return combo_url;

}

[Keith Andersen]

I will be on my computer this evening. I'll check it out.

Tks

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a19824aa-e6b7-40f2-aa73-e8252c95056dn%40googlegroups.com.

[Keith Andersen]

I'm not seeing a need for a function - getSheetUrl() of the active sheet?  In WriteGaffers() you already get the active sheet with - Spreadsheet app.getActiveSpreadsheet().getActiveSheet()

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Keith Andersen]

You can't run a function from a hyperlink.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG8g0kX08qF0e2DgknGp3s0MjB4Xba70fUUZCwoZ5u0xg%40mail.gmail.com.

[ryan rogers]

Well getSheetUrl() worked within that hyperlink. It's seeming like I should probably add a button to call those 2 functions. 

However, I still get a timeout when I try to run the COMBOPLATTER() function from within the scripts editor. Each part works fine on its own. 

[Keith Andersen]

Ryan, is there an onOpen function in the sheet opened by the hyperlink? There is not one in the one you shared.

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b511b658-1067-41fe-99cc-0de914c0efb2n%40googlegroups.com.