0

I have a project where on a single spreadsheet, I'll have some sort of an array like this for every row.

[username, dropdown option, website, dropdown option, button to trigger a script, returnValueByScript1, returnValueByScript2]

So there's a script on the google spreadsheet that has to read 2 values, then proceed to call a API that returns some stuff, make some calculations on that stuff then return 2 values to the array (returnValueByScript1, returnValueByScript2).

The core of the script I've completed successfully, now I need to know if its possible from google spreadsheets to have multiple rows of the same type of arrays (for instance [A2,B2,C2,D2,E2,F2,G2],[A3,B3,C3,D3,E3,F3,G3] and so on) to have some kind of a button that triggers the script and the script detects the row where it was triggered and reads the arguments from the cells of the respective row and exports the arguments to the cells of the respective row. For instance I trigger something on E5, script knows I triggered from row 5, reads C5, D5, does some stuff then writes on F5,G5.

If it's possible what type of functions should I look for.

Harmonica1337
  • 1
  • 1
  • 1
    https://developers.google.com/apps-script/guides/triggers/ You probably want `function myFunctionWithAnInstalledOnEditTrigger(e)` and not `function onEdit(e)` if you need to access things that are not available anonymously, or if your script takes longer than 30s to run. There are lots of "how do make an on edit function do ____" here on SO already, so make sure you do your due diligence :) – tehhowch Mar 27 '18 at 21:26
  • Hello tehhowch, the function onEdit(e) works wonders and thats the right function but i'm facing a problem like this https://stackoverflow.com/questions/49120632/google-spreadsheets-you-do-not-have-permission-to-call-fetch?noredirect=1&lq=1 – Harmonica1337 Mar 30 '18 at 21:32
  • Hello tehhowch, the function onEdit(e) works wonders and thats the right function but i'm facing a problem like this https://stackoverflow.com/questions/49120632/google-spreadsheets-you-do-not-have-permission-to-call-fetch?noredirect=1&lq=1 I can run the UrlFetchApp.fetch outside of function onEdit(e) but inside it always throws the permission error, i've already added the scopes into appsscript.json, still the same problem. How do you suggest to approach this problem? – Harmonica1337 Mar 30 '18 at 21:40
  • Because simple triggers run without requiring authorization, they are [limited in scope](https://developers.google.com/apps-script/guides/triggers/#restrictions) to only those functions that do not need authorization. This is why I mentioned "You probably want `function myFunctionWithAnInstalledOnEditTrigger(e)` and not `function onEdit(e)`" The solution is to rename your function (so it does not activate the simple trigger) and create an installed trigger for your renamed function. – tehhowch Mar 30 '18 at 22:44
  • this is what i have currently https://pastebin.com/S9kw8QVR, changed the appsscript.json to this https://pastebin.com/df8dKYi0 , if i run it without the UrlFetchApp.fetch(url) and the rest it works but if i don't i have the authorization problem like on that link. I've already changed the name of the function of onEdit(e) to something like installedtrigger(e), i proceed to go to "Edit"->"All Your Triggers", set up the installedtrigger(e) to be run, events from spreadsheet and "On Change", save and it gives me an error on the execution script saying it can't call method "getRow" its undefined – Harmonica1337 Mar 30 '18 at 23:07
  • you want the `on edit` event, not the `on change` event. (The event objects are different, as are the triggering criteria). – tehhowch Mar 31 '18 at 01:20
  • that was the problem, everything great now, much appreciated. – Harmonica1337 Mar 31 '18 at 15:16

1 Answers1

0

By clicking on any cell(s) and then calling this function form a button or menu will give you the A1 notation of the clicked cell.

function getRange () {

  var range = SpreadsheetApp.getActiveRange();
  var a1 = range.getA1Notation();

}

This is the same function used to get the row index. 

function getRange () {

  var range = SpreadsheetApp.getActiveRange();
  var rowIndex = range.getRowIndex()

}
James D
  • 3,102
  • 1
  • 11
  • 21