Sending the number of products via email in Google Sheets

I have a google sheets tool where I automatically get the number of products on our site every hour with the Shopify API. https://docs.google.com/spreadsheets/d/1m1lF6jLWPIKhJDOut_G-2tX_zYW4fU9dHKwIs03rr1Y/edit?usp=sharing

I need to develop a code for this tool. This code will check this product number after each product number search and if this product number is below 1000, it should send me a mail informing the product number. How can I improve this?

The function that takes the number of products from the site

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();
};

The function that should take the Products Count number of the last row and send mail if it is less than 1000. (It is not working and has not been completed yet.) https://gyazo.com/e2fd5cdc4043dec00da1fce0e3c6c0ca (6562 is products count)

function SendEmail() {
  // Fetch the monthly sales
  var spreadsheet = SpreadsheetApp.getActiveSheet();

  // I need your help in here. How can I get the products count in the last row after each run?

  // Check totals sales
  if (productsCount < 1000){ 
  // Send Alert Email.
  var message = 'KolayOto Lastik Ürün Sayısı Uyarısı!'; // Second column
  var subject = 'Ürün sayısı uyarısı!';
  MailApp.sendEmail("bbelcioglu@sekizgen.com", subject, message);
  MailApp.sendEmail("berk.belcioglu@gmail.com", subject, message);
  }
}

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

Thanks.

I believe your goal as follows.

• You want to retrieve the Products Count number from the URL using Google Apps Script.
  • In this case, you want to return the value of 6562.
• You want to return that value at the function of _1().

For this, how about this answer?

Modification points:

• When the returned value from the URL is seen, the Products Count number can be retrieved with contentObj.products_count.

Modified script:

When your script of _1() is modified, it becomes as follows.

From:

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

To:

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

  return contentObj.products_count;  // <--- Added
};

And, by above modification, SendEmail() can be modified as follows.

function SendEmail() {
  var productsCount = _1(); // <--- Added

  // Fetch the monthly sales
  var spreadsheet = SpreadsheetApp.getActiveSheet();

  // I need your help in here. How can I get the products count in the last row after each run?

  // Check totals sales
  if (productsCount < 1000){ 
  // Send Alert Email.
  var message = 'KolayOto Lastik Ürün Sayısı Uyarısı!'; // Second column
  var subject = 'Ürün sayısı uyarısı!';
  MailApp.sendEmail("bbelcioglu@sekizgen.com", subject, message);
  MailApp.sendEmail("berk.belcioglu@gmail.com", subject, message);
  }
}