Automatically track the number of products with the Shopify API in Google Sheets

Question

I recently started working and learning on Google Sheets and encountered a few problems I couldn't solve.

I want to automatically track the number of products on our Shopify site every hour via Google Sheets with the Shopify API.

For this, I used the tutorial I gave the link below:

https://medium.com/@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minutes-a3fede1a014

Tutorial works, I can get the number of our products, but when I want to do this automatically with macros every hour, macros always work on the same row.

https://gyazo.com/6fe0710c7537d2d75249f28810276d43

I want the macro to work on the first row, go down 2 rows and work the same way from where it left off after 1 hour.

The macro code I added with relative references:

function _1() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  spreadsheet.getActiveRange().setFormulaR1C1('=ImportJSON("https://kolayoto.com/collections/lastikleri.json")');
  spreadsheet.getCurrentCell().offset(2, 0).activate();
};

Also,

The API results I get are replaced with the current results for the following hours when I renew the page. Is there any way to convert this to an constant value after every automated API call? Can we set this in macro too?

I hope I could explain the problems enough. I would be glad if you help.

Thanks.

Answer 1

If I understand your question correctly, what you are looking for is the function spreadsheet.getLastRow(). spreadsheet.getLastRow()+1 will return the next available empty row.

Also, try using UrlFetchApp fetch approach so that you can be more flexible on your json keys and values. Try run below code serval times start from a blank sheet and see if this is what you really want. Basically what I did is to picking the keys and values of the json data and set them to range accordingly.

function _1() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var startCol = 1
  var lastRow = spreadsheet.getLastRow();

  var rawtext = UrlFetchApp.fetch("https://kolayoto.com/collections/lastikleri.json").getContentText()
  var jsonObj = JSON.parse(rawtext);
  var contentObj = jsonObj.collection

  var columns = Object.keys(contentObj)

  var colName = []
  var result = []
  for (var i = 0; i < columns.length; i++){
    var key = columns[i]
    var value = contentObj[key]
    result.push(value)
  }

  var numRow = 1
  var numCol = columns.length
  if(lastRow+1 == 1){
    spreadsheet.getRange(lastRow+1, startCol, numRow, numCol).setValues([columns]);
    spreadsheet.getRange(lastRow+2, startCol, numRow, numCol).setValues([result]);
  }else {
    spreadsheet.getRange(lastRow+1, startCol, numRow, numCol).setValues([result]);
  }

  spreadsheet.getRange(lastRow+1, startCol).activate();
};