Google Sheets and Appscript doesn't update the function properly

Question

I have ImportJson() function that doesn't update its value unless I make changes to Appscript then reload the sheet again, I think it was running but suddenly stopped.

The function should be get the value of 'A2' if found go to the loop, if not just return the fixed API call data, but whatever I update the A2 cell it doesn;t recall the function with the other kinds of the API call but it runs normally in the app script editor

  function ImportData1() {


  var Final_result = [];
  var VenueId_results = [];

  var PageNumber_index = 0;
  var Page_number = [0, 1, 2, 3, 4];
  var New_URL = "";

  var url = "https://app.ticketmaster.com/discovery/v2/events?sort=venueName,asc&locale=*&size=199&" + "page=" + Page_number[PageNumber_index] + "&apikey=" + API_key + "&venueId=" + venueIds;
  // ImportJSON(url, "/","noInherit,noTruncate,rawHeaders");
  // console.log(ImportJSON(url, "/", "noInherit,noTruncate,rawHeaders"));

  //  console.log("Veuneid" + Veunue_id + Venue_Id_List.length);
  console.log("ImportData1();" + Venue_Id_List.length);

  var New_Venue = SpreadsheetApp.getActiveSheet().getRange('A2').getValue();
  console.log(New_Venue);
  if ( New_Venue != "") {



    var Venue_arr = New_Venue.split(",");



    VenueId_results = Add_new_VeunueId(Venue_arr);

    var Last_New_Id_Venue = "";

    for (var Index_venune = 2; Index_venune < (VenueId_results.length) - 2; Index_venune++) {

      console.log("Venuesid " + VenueId_results[Index_venune]);
      var New_Id_Venue = VenueId_results[Index_venune].toString() + ",";

      Last_New_Id_Venue += New_Id_Venue;
      console.log("New_Id_Venue " + New_Id_Venue);

    }
    console.log("Last_New_Id_Venue " + Last_New_Id_Venue);

    New_URL = url + Last_New_Id_Venue;

    New_Venue = "";
    VenueId_results = [];

    // return ImportJSON(New_URL, "/_embedded/events/name,/_embedded/events/url,/_embedded/events/_embedded/venues/name,/_embedded/events/dates/start/localDate,/_embedded/events/dates/start/dateTime,/_embedded/events/priceRanges/min,/_embedded/events/priceRanges/max,/_embedded/events/_embedded/venues/url,/_embedded/events/_embedded/venues/city/name", "noInherit,noTruncate,rawHeaders");
    for (; PageNumber_index < Page_number.length; PageNumber_index++) {

      console.log("looopsyes");

      Final_result = Final_result.concat(ImportJSON(New_URL, "/_embedded/events/name,/_embedded/events/url,/_embedded/events/_embedded/venues/name,/_embedded/events/dates/start/localDate,/_embedded/events/dates/start/dateTime,/_embedded/events/priceRanges/min,/_embedded/events/priceRanges/max,/_embedded/events/_embedded/venues/url,/_embedded/events/_embedded/venues/city/name", "noInherit,noTruncate,rawHeaders"));
      console.log("New_URL " + PageNumber_index + Page_number[PageNumber_index] + Final_result);

      Utilities.sleep(1000);
    }

    console.log("New_URL " + New_URL);
    console.log("Final_result " + Final_result);
    return Final_result;
    console.log("New_URL " + Page_number);




    return Final_result;
    //  url += New_Venue;

    var New_URL = url + New_Venue;
    console.log("hello" + New_URL);


  } else {
    console.log("hellono");
    New_Venue = "";
    VenueId_results = [];
    return ImportJSON(url, "/_embedded/events/name,/_embedded/events/url,/_embedded/events/_embedded/venues/name,/_embedded/events/dates/start/localDate,/_embedded/events/dates/start/dateTime,/_embedded/events/priceRanges/min,/_embedded/events/priceRanges/max,/_embedded/events/_embedded/venues/url,/_embedded/events/_embedded/venues/city/name", "noInherit,noTruncate,rawHeaders");

  }


  // /_embedded/events/name,/_embedded/events/url,/_embedded/events/dates/start/localDate,/_embedded/events/dates/start/dateTime,/_embedded/events/priceRanges/min,/_embedded/events/priceRanges/max,/_embedded/events/_embedded/venues/name,/_embedded/events/_embedded/venues/url,/_embedded/events/_embedded/venues/city/name






  // setTimeout(import_data, 5000);



}

@Cooper it's returning an array of venues of `A2` cell string that when there is values in A2 I get the value and make API calls for these values then return the values then proceed — Mark, May 28 '22 at 19:01
How is the function triggered? The function just grabs the value in A2 once and does not go back to re-check it. So unless you are triggering the script to run every time the value changes, it is not looking at any refresh in that cell. — George, May 28 '22 at 19:15
I'm calling the function `=ImportData1()` should it calls itself automatically once there is an update in `A2` cell — Mark, May 28 '22 at 19:22
@Cooper It's defined but outside the function as a global variable because it's fized with the API call. — Mark, May 28 '22 at 19:30
The problem is even with deleting `A2` or adding value to the cell I get the same past reulst unless I edit the code script the result change. — Mark, May 28 '22 at 19:31
if you want the script to re-run every time a new value is in A2, then yes, setup a trigger, make it based on Spreadsheet Edit. Your script would need to look at the event object and only if the spreadsheet cell A2 is the one that caused the event should your other function run. — George, May 28 '22 at 19:38
Ok I understand, But when I was calling `ImportJson()` from the popular github script(I get the data by same function btw) the sheet was updating the call and the data automatically. — Mark, May 28 '22 at 19:48
May I ask How to trigger `A2` cell changing value by calling `ImportData1()` in `A9`? — Mark, May 28 '22 at 19:49
Maybe this's the problem because when I run the function in app script it runs normally. — Mark, May 28 '22 at 19:50
See duplicate above your question. Just call `=IMPORTDATA1(A2)` — TheMaster, May 28 '22 at 20:17
@TheMaster It works!!, Can you submite an answer to verify for future candidates. Thanks for all guys. pretty appreciate. — Mark, May 28 '22 at 20:32
Hi... This is a duplicate. See linked question above. It's already been asked and answered. I don't think I can add anymore to that. You don't have the necessary reps(15), but in the future if you do, you can upvote my answer or other answers in the duplicate. — TheMaster, May 28 '22 at 23:03

score 0 · Answer 1 · answered May 28 '22 at 19:46

0

Here is a simple function that you can setup to trigger every time cell A2 changes. It will call your import function when the right cell has changed.

function valueChange(e)
{
  if (e.range.getRow() == 1 && e.range.getColumn() == 2)
  {
    ImportData1();
  }
}

answered May 28 '22 at 19:46

George

51
5

I get this error `TypeError: Cannot read property 'range' of undefined` but is that trigger the `ImportData1()` function in `A9` ? – Mark May 28 '22 at 20:20
I understand it get the realtime or it execute the function in any cell wherever right? – Mark May 28 '22 at 20:21
The variable e is only available when the function (valueChange) is triggered automatically on a change to value A2. So you can not just run the function manually. Set up a trigger for the spreadsheet on edit and use this valueChange as the function to call on the trigger. Then test it by changing the value in A2. – George May 29 '22 at 21:07

Google Sheets and Appscript doesn't update the function properly

1 Answers1

Linked