Script to import from Pipedrive to google sheets for last 20 days

Question

I have this script for importing from Pipedrive to Google sheets that works great. I would like it to only pull the matches from the last 20 days instead of from a specific date. I would also like to clear the rows each time and ass them in fresh rather than add them to the next empty row.

function GetPipedriveDeals() {
  var ss = SpreadsheetApp.openById('sheet name');
  var sheet = ss.getSheetByName("Sheet1");

  var url    = "https://api.pipedrive.com/v1/activities?user_id=0&start=0&limit=500&start_date=2017-06-01&api_token=xxxxxxxxxxxxxxxxxxxx";

  var response = UrlFetchApp.fetch(url); 
  var dataSet = JSON.parse(response.getContentText()); 
  var data;

  for (var i = 0; i < dataSet.data.length; i++) {
    data = dataSet.data[i];
    sheet.appendRow([data.user_id, data.type, data.add_time, data.note, data.org_name]);
  }
}

Any help would be greatly appreciated, thank you in advance.

Answer 1

You can use start_date & end_date parameter to fetch last 20 days records and sheet.clear() to clear the contents of the sheet. Refer the below code. Hope this helps!

   function GetPipedriveDeals() {
      var ss = SpreadsheetApp.openById('sheet name');
      var sheet = ss.getSheetByName("Sheet1");

      sheet.clear(); //clear the contents of the sheet

      var startDate = getStartDate();
      var endDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
      var url    = "https://api.pipedrive.com/v1/activities?user_id=0&start=0&start_date="+startDate+"&end_date="+endDate+"api_token=xxxxxxxxxxxxxxxxxxxx";
      var response = UrlFetchApp.fetch(url); 
      var dataSet = JSON.parse(response.getContentText()); 
      var data;

      for (var i = 0; i < dataSet.data.length; i++) {
        data = dataSet.data[i];
        sheet.appendRow([data.user_id, data.type, data.add_time, data.note, data.org_name]);
      }
    }


    function getStartDate() {
      var result = new Date();
      result.setDate(result.getDate() - 20);
      return result.convertDate();
    }

    Date.prototype.convertDate = function() {
      var mm = this.getMonth() + 1; 
      var dd = this.getDate();

      return [this.getFullYear(),
         (mm>9 ? '' : '0') + mm,
         (dd>9 ? '' : '0') + dd
      ].join('-');
    };