google sheet script inserting data into sheet

Question

So i have a script the gets some data from a server.
I want my script to publish the data to a sheet name "market items".
I got this working if I'm running the script directly from the sheet by using =getMarketItemsTrigger(1).
It posts all 11,669 items to my sheet.

The problem with this is that it refreshes every time the sheet is reloaded; I need it to only run once a month.
I've been trying to create a script which needs no reference in the given sheet but posts directly to a pre-named sheet but I can't figure out how I can get the data into the sheet

this is the script file i'm using

var version = '9a'
function getMarketItemsTrigger(refresh)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Market Items");
  
  Logger.log(sheet.getMaxColumns());
  Logger.log(sheet.getMaxRows());
  sheet.clear();
 
  
  if(sheet.getMaxColumns()>2){
    Logger.log('deleting colums');
  sheet.deleteColumns(2, sheet.getMaxColumns()-2);
    
  }
  if(sheet.getMaxRows()>2){
     Logger.log('deleting rows');
   sheet.deleteRows(2,sheet.getMaxRows()-1);
  }
  
    var marketItemsEndpoint = 'https://crest-tq.eveonline.com/market/types/';
  var marketItemsResponse = JSON.parse(fetchUrl(marketItemsEndpoint));

  var totalPages = marketItemsResponse['pageCount'];

  var itemList = [];
  var headers = ['Item Name', 'ID'];
  itemList.push(headers);

  
  for (var currentPage = 1; currentPage <= totalPages; currentPage++)
  {
    Logger.log('Processing page ' + currentPage);
    var marketItems = marketItemsResponse['items'];
    for (var itemReference in marketItems)
    {
      var item = marketItems[itemReference];
      itemList.push([item['type']['name'], item['id']]);
      
     
    }

    if (currentPage < totalPages)
    {
      var nextEndpoint = marketItemsResponse['next']['href'];
      marketItemsResponse = JSON.parse(fetchUrl(nextEndpoint));
    }
    
    
    
  }
 
  //sheet.insertRows(1,itemList.length+1);
 // var range = sheet.getRange(1, 1,itemList.length+1,3);
  
//  for(var i = 1;i<itemList.length;i++){
    
//   range.getCell(i, 1).setValue([itemList]);
//    range.getCell(1, i).setValue(itemList.);
  //  }
 // Logger.log("don");
//sheet.getRange(1, 1, 1, itemList.length).setValues(itemList);
  
//  sheet.getRange(itemList.length+1, 2).setValues(itemList);
 // sheet.getDataRange().setValues([itemList]);
//  sheet.appendRow(itemList);
//  sheet.getRange(12+totalPages, 1, itemList.length, 1).setValues(itemList);
  return itemList;
}
/**
 * Private helper method that wraps the UrlFetchApp in a semaphore
 * to prevent service overload.
 *
 * @param {url} url The URL to contact
 * @param {options} options The fetch options to utilize in the request
 */
function fetchUrl(url)
{
  if (gcsGetLock())
  {
    // Make the service call
    headers = {"User-Agent": "Google Crest Script version " + version + " (/u/nuadi @Reddit.com)"}
    params = {"headers": headers}
    httpResponse = UrlFetchApp.fetch(url, params);
  }
    
  return httpResponse;
}


/**
 * Custom implementation of a semaphore after LockService failed to support GCS properly.
 * Hopefully this works a bit longer...
 *
 * This function searches through N semaphores, until it finds one that is not defined.
 * Once it finds one, that n-th semaphore is set to TRUE and the function returns.
 * If no semaphore is open, the function sleeps 0.1 seconds before trying again.
 */
function gcsGetLock()
{
  var NLocks = 150;
  var lock = false;
  while (!lock)
  {
    for (var nLock = 0; nLock < NLocks; nLock++)
    {
      if (CacheService.getDocumentCache().get('GCSLock' + nLock) == null)
      {
        CacheService.getDocumentCache().put('GCSLock' + nLock, true, 1)
        lock = true;
        break;
      }
    }
  }
  return lock;
}
/**
 * Private helper function that will check for a new version of GCS.
 */
function versionCheck()
{
  var versionEndpoint = 'https://raw.githubusercontent.com/nuadi/googlecrestscript/master/version';
  var newVersion = fetchUrl(versionEndpoint);

  if (newVersion != null)
  {
    newVersion = newVersion.getContentText().trim();
    Logger.log('Current version from Github: ' + newVersion);

    var message = 'You are using the latest version of GCS. Fly safe. o7';
    var title = 'No updates found';
    if (newVersion > version)
    {
      message = 'A new version of GCS is available on GitHub.';
      title = 'GCS version ' + newVersion + ' available!';
    }
    SpreadsheetApp.getActiveSpreadsheet().toast(message, title, 120);
  }
}

All the code in the function getMarketItemsTrigger that's commented out is what I have tyred without luck .

The short version of this question is how can i post the values in itemList to column a and b in sheet market items

Hi, a google script doesn't have to be a custom function (to be used as a formula). You can also write the array itemList directly to the sheet (from within the script. That function can then be triggered using a time base trigger (once a month). — JPV
but how can i write the itemList from the script to the sheet? — simondid
ss.getSheetByName('name_of_sheet_here').getRange(1, 1, itemList.length, itemList[0].length).setValues(itemList) -->> change sheet name and range to suit. — JPV
JPV please make an answer so i can accept it you're suggestion is working — simondid

JPV JPV · Accepted Answer · 2017-01-27T14:15:40

You can write the array itemList to the sheet by adding:

//your code
ss.getSheetByName('name_of_sheet_here')
.getRange(1, 1, itemList.length, itemList[0].length)
.setValues(itemList) 
//more code (if needed)
} //end of code

-->> change sheet name and range to suit.

google sheet script inserting data into sheet

2 Answers