CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Get Her Done
Get Her Done

Reputation: 31

Why Isn't My Spreadsheet Changing From Input Values of An Outside Source?

So I am trying to use an outside source (Zapier) to input values into my spreadsheet. These input values are then "transposed (formula wise)" into my spreadsheet to fit the cell coordinates with which they are to align.

I have the spreadsheet set to run 'onEdit' and when these incoming values arrive, it is supposed to cause the rest of the spreadsheet to change, but the function is not running.

However, if I were to edit the spreadsheet 'manually,' the onEdit function runs perfectly.

So why then would the spreadsheet not be running the function, when the outside source brings its input values?

UPDATE:

So I discovered that if I manually authorize an 'onChange' installable trigger, it will work. But if I create a copy of the same exact spreadsheet, the installable trigger will not exist in the copy. The copy needs to have the trigger without me having to do it manually. So I am trying to create a code inside of Google Script Editor that will either allow me to use the onChange function or install the onChange function in the Developer Hub. Any thoughts? Here is the code I tried but did not work:

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("My 
     Sheet") 

var ssid = "My SpreadSheet ID"; 
     ScriptApp.newTrigger('My Sheet) 
    .forSpreadsheet(ss.getId()) 
    .onChange() 
    .create(); 

    myFunction()
    {

If there is an alternative for the onChange function, then I'm all ears. I just need a function that can run itself in my copies.

Upvotes: 2

Views: 147

Answers (1)

0Valt
0Valt

Reputation: 10345

As a part of a collaborative effort, let me clarify the Zapier part (this answer does not concern the copy part).

Part 1. Zapier setup

Assuming you have a third-party application that you pull data from (btw, since you decided to use apps script, isn't it easier to drop the middleman like Zapier and connect to the 3P app API – if it has one, ofc – directly?), you created a Catch Hook and a POST Action.

The POST Action setup contains several fields:

  1. URL field - this is where your /exec URL goes (WebApp is deployed via Publish->Deploy as a WebApp). After you deploy your script as a WebApp, you will get a URL that users and scripts can make requests to (it is always of this format https://script.google.com/macros/s/{yourProjectId}/exec - with some slight diff. due to access permissions). To avoid permissions issue, set the Who has access to the app option to anyone or anyone, even anonymous (otherwise, you'll have to devise auth handling).
  2. Payload Type field is irrelevant here, but I suggest using JSON.
  3. Data field is required if you chose the POST Action and should contain key-value pairs of data you would like to transmit via Zapier (the data will be available in parameter/parameters property of the event object).

Part 2. WebApp setup

Published WebApps should have either a doGet() or doPost() function to be able to receive and process requests (or both). Each of them accepts one special argument, which is constructed each time a request to the WebApp is made – an event object.

The event object will contain all the data that you sent from Zapier. You can then use this data to conditionally trigger different functions, pass data to handlers, etc. So, instead of relying on triggers, you can create a function that is called inside the doGet / doPost that will a) populate your target sheet with new values; b) do anything else after that, thus acting as an analogue of onEdit / onChange.

Useful links

  1. Event object structure;
  2. Passing event objects around;
  3. Creating triggers on other documents;

Upvotes: 0

Related Questions