Google Sheets Apps Script integration with Lightspeed X series

Question

I cannot tell why this script is not writing to my sheet. I have modified the URL, apiKey, and google sheets ID obviously. I am trying to get sales data from the previous day to populate into google sheets from Lightspeed X-Series API. I would like the specific data headers, but would be open to any other examples that help me get the ball rolling.

Any help would be great:

function getLightspeedSalesData() {
  // Set the Lightspeed x Series API endpoint.
  var endpoint = 'https://xyz.vendhq.com/api/2.0/sales';

  // Set the Lightspeed x Series API key.
  var apiKey = 'uniqueKEY';

  // Set the date range for the sales data.
  var startDate = new Date();
  startDate.setDate(startDate.getDate() - 1);
  var endDate = new Date();

  // Create a new request to the Lightspeed x Series API.
  var request = UrlFetchApp.fetch(endpoint, {
    method: 'GET',
    headers: {
      'Authorization': 'Bearer ' + apiKey
    },
    params: {
      'start_date': startDate.toISOString(),
      'end_date': endDate.toISOString()
    }
  });

  // Get the response from the Lightspeed x Series API.
  var response = request.getContentText();

  // Parse the response as JSON.
  var data = JSON.parse(response);

  // Return the sales data.
  return data.sales;
}

function populateLightspeedSalesData() {
  // Get the Lightspeed x Series sales data.
  var salesData = getLightspeedSalesData();

  // Get the Google Sheets spreadsheet.
  var spreadsheet = SpreadsheetApp.openById('GOOGLEsheetsID');

  // Get the worksheet where the sales data will be populated.
  var worksheet = spreadsheet.getSheetByName('Sales Data');

  // Clear the worksheet.
  worksheet.clearContents();

  // Set up the header row (assuming the first row is for headers)
  worksheet.getRange(1, 1).setValue('SKU');
  worksheet.getRange(1, 2).setValue('SKU Name');
  worksheet.getRange(1, 3).setValue('Supplier Code');
  worksheet.getRange(1, 4).setValue('Brand');
  worksheet.getRange(1, 5).setValue('Supplier');
  worksheet.getRange(1, 6).setValue('Category');
  worksheet.getRange(1, 7).setValue('Items Sold');
  worksheet.getRange(1, 8).setValue('Remaining Inventory');

  // Write the sales data to the worksheet, starting from the second row (row 2).
  for (var i = 0; i < salesData.length; i++) {
    var sale = salesData[i];
    var row = i + 2; // Start from the second row

    worksheet.getRange(row, 1).setValue(sale.product.sku);
    worksheet.getRange(row, 2).setValue(sale.product.name);
    worksheet.getRange(row, 3).setValue(sale.product.supplier_code);
    worksheet.getRange(row, 4).setValue(sale.product.brand);
    worksheet.getRange(row, 5).setValue(sale.product.supplier);
    worksheet.getRange(row, 6).setValue(sale.product.category);
    worksheet.getRange(row, 7).setValue(sale.quantity);
    worksheet.getRange(row, 8).setValue(sale.product.inventory_remaining);
  }
}

Answer 1

It appears to me that salesData is a two dimensional arrary so it's pretty much ready to go into the spreadsheet.

function populateLightspeedSalesData() {
  const hr = ['SKU','SKU Name','Supplier Code','Brand','Supplier','Category','Items Sold','Remaining Inventory']
  var salesData = getLightspeedSalesData();
  var ss = SpreadsheetApp.openById("spreadsheet id");
  var sh = ss.getSheetByName('Sales Data');
  salesData.unshift(hr);  
  sh.clearContents();
  sh.getRange(1,1,salesData.length,salesData[0].length).setValues(salesData);
}

• google-apps-script reference
• javascript reference
• Learn More
• Range.getValues()Array.unshift()
• Array.unshiftt()