google-sheetsgoogle-apps-scriptcustom-function

Make a logger based on ImportJSON output, triggered per minute


I am making a Google Spreadsheets that logs everytime the output of the formula that uses ImportJSON. The outcome of the picture should be comparible to this image:

enter image description here

Now, to give some insight, I know I can use a trigger to call a function every minute and "log" the outcome, although I am overthinking the code in Google Appscript presumably.

Context: In A1 there is a parameter, used to determine the link it gets its information for with the formula in A2

=IF(A1 = "", "", ImportJSON("https://example.com/api/public/users?name=" & A1, "/online","noHeaders"))

Obviously the above is an example URL so don't expect to extract data (though my website uses API).

As said, I am overthinking probably, so please do help me out. I am not the best in Appscript. Could someone suggest a code that would work the way it is discribed? I don't get much further then a line that puts every new outcome in a new row, but the actual code is unknown to me and other posts on Stackoverflow did not give much info or was tailored to Python etc.

Destination Tab is called Data


Solution

  • Use Sheet.appendRow(), like this:

    function logDataA2() {
      const ss = SpreadsheetApp.getActive();
      const sourceCell = ss.getRange('Sheet1!A2');
      const targetSheet = ss.getSheetByName('Data');
      targetSheet.appendRow([new Date(), sourceCell.getValue());
    }
    

    See Sheet.appendRow().