Google Apps Script + Pipedrive API “The JavaScript runtime exited unexpectedly.” Error

API
1

The problem I’m getting is: “The JavaScript runtime exited unexpectedly.”

I need help with finding the source for this problem. Script attached below. It was working correclty and at one point it stopped with the error above. What is strange I tried API keys from two other accounts and with one of them it worked but with the other I got the same error.

const getPipedriveData = async () => {
  
  
  const arr = []
  // GET SHEET
    const getSheet = SpreadsheetApp.openById('xxxxxxxx');
  // const getSheet = SpreadsheetApp.create(`PD Deals Report ${new Date().toISOString()}`);
  const sheet1 = getSheet.getSheets()[0]
  const header = [
 "Product Name",
    "Product Quantity",
    "Product Amount",
    "Title",
    "Creator",
    "Owner",
    "Value",
    "Currency",
    "Weighted value",
    "Currency of Weighted value",
    "Probability",
    "Organization",
    "Pipeline",
    "Contact person",
    "Stage",
    "Label",
    "Status",
    "Deal created",
    "Update time",
    "Last stage change",
    "Next activity date",
    "Last activity date",
    "Won time",
    "Last email received",
    "Last email sent",
    "Lost time",
    "Deal closed on",
    "Lost reason",
    "Visible to",
    "ID",
    "Expected close date",
    "Deal value",
    "Currency of Deal value",
    "Authority",
    "Budget",
    "Currency of Budget",
    "Need",
    "Time",
    "VB 500 - number of robots",
    "VB 501 - number of robots",
    "VB 1000 - number of robots",
    "VB 1500 - number of robots",
    "Dolly - number of robots",
    "Fleet Manager - licenses",
    "SQL",
    "MQL",
    "Source",
    "Organiation ID",
    "Partner source",
    "AC Deal ID",
    "MPK",
    "ZO"
  ]
  const setHeader = (list) => {
    list.forEach((value, index) => {
      let range = sheet1.getRange(1, index + 1);
      // console.log(range.getA1Notation())
      range.setValue(value);
    })
  }
  setHeader(header);

  // GET PIPEDRIVE DATA
  const token = 'xxxxxx'

  console.time('getDealsData')
  // DEALS DATA
  const getDealsData = async () => {
    const link = (apiStartNumber) => `https://api.pipedrive.com/v1/deals?status=all_not_deleted&start=${apiStartNumber}&limit=500&api_token=${token}`
    
    let apiStart = 0
    let existMoreData = false
    
    const dealsUrlResponse = await UrlFetchApp.fetch(link(apiStart))
    const dealsUrlData = await JSON.parse(dealsUrlResponse.getContentText())
    let dealsData = dealsUrlData.data
 
    apiStart = dealsUrlData.additional_data.pagination.next_start
    existMoreData = dealsUrlData.additional_data.pagination.more_items_in_collection
    
    while (existMoreData) {
      await new Promise(async (resolve) => {
        const apiResponseNewPage = await UrlFetchApp.fetch(link(apiStart))
        const dealsUrlDataNewPage = await JSON.parse(apiResponseNewPage.getContentText())
        apiStart = dealsUrlDataNewPage.additional_data.pagination.next_start
        existMoreData = dealsUrlDataNewPage.additional
      })
    }
    return dealsData
  }

  const getDealProducts = async (id) => {
      const link = () => `https://api.pipedrive.com/v1/deals/${id}/products?api_token=${token}`
      console.log(link())
      try {
        const dealUrlResponse = await UrlFetchApp.fetch(link())
        const dealUrlData = await JSON.parse(dealUrlResponse.getContentText())
        let dealData = dealUrlData.data
        return dealData
      }
      catch(e) {
        console.log('error:',e )
      }
  }

  console.log('1')
  const allDeals = await getDealsData();
  console.log('all deals:', JSON.stringify(allDeals[0], 0 , 2));

  // const getAllDealsProducts = async (allDeals) => {
    // await allDeals.forEach(async deal => {
    for (let i = 0; i < allDeals.length; i++) {
      const deal = allDeals[i]
      if (0 < deal.products_count) {
      // const deal = allDeals[0]
      // console.log('2')
      // console.log('before:', deal)
      const dealProducts = await getDealProducts(deal.id)
      deal.products = dealProducts
      // console.log('after:', deal)
      // process.exit(0)
      }
      else {
        deal.products = null;
      }
    }
    // })
  // }
  console.log('3')


  const formatDealArray = (deal) => {
    const outDeal = [];
    outDeal.push(deal.title);
    outDeal.push(deal.creator_user_id.name);
    outDeal.push(deal.user_id.name);
    outDeal.push(deal.value);
    outDeal.push(deal.currency);
    outDeal.push(deal.weighted_value);
    outDeal.push(deal.weighted_value_currency);
    outDeal.push(deal.probability);
    if (null !== deal.org_id) outDeal.push(deal.org_id.name);
    else outDeal.push('')
    outDeal.push(deal.pipeline_id);
    if (null !== deal.person_id) outDeal.push(deal.person_id.name);
    else outDeal.push('')
    outDeal.push(deal.stage_id);
    outDeal.push(deal.label);
    outDeal.push(deal.status);
    outDeal.push(deal.add_time);
    outDeal.push(deal.update_time);
    outDeal.push(deal.stage_change_time);
    outDeal.push(deal.next_activity_date);
    outDeal.push(deal.last_activity_date);
    outDeal.push(deal.won_time);
    outDeal.push(deal.last_outgoing_mail_time);
    outDeal.push(deal.last_incoming_mail_time);
    outDeal.push(deal.lost_time);
    outDeal.push(deal.close_time);
    outDeal.push(deal.lost_reason);
    outDeal.push(deal.visible_to);
    outDeal.push(deal.id);
  
    //   deal.products.forEach(product => {
    //     productsQuantity += product.quantity  
    //   })
    //   // console.log(productsQuantity)
    //   outDeal.push(productsQuantity)
    // }
    // else {
    //   outDeal.push(null); //product quantity
    // }
    //outDeal.push(deal.products_count);
    outDeal.push(deal.expected_close_date);
    outDeal.push(deal['70f797262397f14ab4275ab622a839401df5744c']); //deal value = 70f797262397f14ab4275ab622a839401df5744c
    outDeal.push(deal['70f797262397f14ab4275ab622a839401df5744c_currency']); //currency of deal value  = 70f797262397f14ab4275ab622a839401df5744c_currency
    outDeal.push(deal['7ee34f4eaf4070ee19a94720d1ea2f60ebaad492']); //authority = 7ee34f4eaf4070ee19a94720d1ea2f60ebaad492
    outDeal.push(deal['7008414300788c0dcdfc3c984c36f43a98a70563']); //budget = 7008414300788c0dcdfc3c984c36f43a98a70563
    outDeal.push(deal['7008414300788c0dcdfc3c984c36f43a98a70563_currency']); //Currency of budget = 7008414300788c0dcdfc3c984c36f43a98a70563_currency
    outDeal.push(deal['29b74d61dcdde77d30f20bd79936afb43a63da9c']); //Need = 29b74d61dcdde77d30f20bd79936afb43a63da9c
    outDeal.push(deal['8f48bed4f144866662c05a0017a3fb4d69384a02']); //Time = 8f48bed4f144866662c05a0017a3fb4d69384a02
    outDeal.push(deal['ba0d47735044c78c77a6d2ca919fc941cc8a32cc']); //"VB 500 - number of robots", = ba0d47735044c78c77a6d2ca919fc941cc8a32cc
    outDeal.push(deal['dda37fa705c12aeae32ced980b3ffdad10dfa24e']); //"VB 501 - number of robots", = dda37fa705c12aeae32ced980b3ffdad10dfa24e
    outDeal.push(deal['3ed1d126d433b0b3ce7bee3814eaace1c10f22ff']); //"VB 1000 - number of robots", = 3ed1d126d433b0b3ce7bee3814eaace1c10f22ff
    outDeal.push(deal['0d16703cd50c581a0993bace75f03fcff11d5c18']); //"VB 1500 - number of robots", = 0d16703cd50c581a0993bace75f03fcff11d5c18
    outDeal.push(deal['1360123c4aa2a2552593529eb9af4145ce9eace7']); //"Dolly - number of robots", = 1360123c4aa2a2552593529eb9af4145ce9eace7
    outDeal.push(deal['9265bea2726a43467fce1f2cb1a7290cffc2bca7']); //"Fleet Manager - licenses", = 9265bea2726a43467fce1f2cb1a7290cffc2bca7
    outDeal.push(deal['dc54c808aed2d55a6d1a87186f7661faea60de3b']); //"SQL" = dc54c808aed2d55a6d1a87186f7661faea60de3b
    outDeal.push(deal['174594fe049f8720d001b78062f19dc24b1ee556']); //"MQL" = 174594fe049f8720d001b78062f19dc24b1ee556
    outDeal.push(deal['8d399a84c5259f71a778d24fe72d9f12c84b187b']); //"Source" = 8d399a84c5259f71a778d24fe72d9f12c84b187b
    outDeal.push(deal['8300cdf056ce98627ec283cc3dd6779f9505febd']); //"OrganizationID" = 8300cdf056ce98627ec283cc3dd6779f9505febd
    outDeal.push(deal['68330061726521c78d5f7325722b602b15d1dae8']); //"Partner Source" = 68330061726521c78d5f7325722b602b15d1dae8
    outDeal.push(deal['b5c20cbaf3a074bcd70a3a258cb97a7ffba9c4cf']); //"AC Deal ID" = b5c20cbaf3a074bcd70a3a258cb97a7ffba9c4cf
    outDeal.push(deal['51895362d71a166511224f7b331ac87ddfde1cb8']); //"MPK" = 51895362d71a166511224f7b331ac87ddfde1cb8
    outDeal.push(deal['88c972476c81b991ab749613164d197d41810aed']); //"ZO" = 88c972476c81b991ab749613164d197d41810aed
  //   // console.log(
    
    if (null !== deal.products) {
      const productsArray = [];
      // let productsQuantity = 0;
      deal.products.forEach(product => {
        const productData = [];
        productData.push(product.name) //Product Name
        productData.push(product.quantity) //Product Quantity
        productData.push(product.item_price) //Product Amount
        productData.push(...outDeal) //Associated deal details
        productsArray.push(productData);
        // productsQuantity += product.quantity  
      })
      return productsArray;
      // console.log(productsQuantity)
      // outDeal.push(productsQuantity)
    }
    else {
      return [['', '', '', ...outDeal]]; //empty product (array of one deal)
    }

    return outDeal;
  }
  const productsArray = [];
  allDeals.forEach((deal) => {
    productsArray.push(...formatDealArray(deal))
  })
  // const dealsArrays = allDeals.map(deal => formatDealArray(deal));
  
  const fillDeals = (productsArray) => {
    // console.log(dealsArray.length)
    let range = sheet1.getRange(2, 1, productsArray.length, productsArray[0].length);
    range.setValues(productsArray);
  }
  // fillCreator(formattedDeals)
  // fillOwner(formattedDeals)
  fillDeals(productsArray)
}

Appreciate any suggestions

2

Hello @pawel.kusina

This error:

Seems like connected to Google Apps Script and not Pipedrive API.

I would suggest using these techniques 疑難排解  |  Apps Script  |  Google for Developers to debug the issue.

As it’s connected to your Google App Script setup, the script and Pipedrive data connected via API token, it’s hard to say what’s going wrong, but I assume that const allDeals = await getDealsData(); might cause some problems if there are too many deals in Pipedrive and script can’t handle them properly.

3

Hi @pawel.kusina

Apps script is a synchronous. So convert everything to normal functions. And try to use batch operations. So for instance: Setting the header values is done in one cal:

  //Setting header in one call needs to be a 2d array. first array for the row, nested array are the columns..
  sheet1.getRange(1,1,1,header.length).setValues([header])

Her is a example i use to create or update an item in pipedrive. If you need help i’m happy to help.

function sendItemToPipedrive(data) {
  const env = PropertiesService.getScriptProperties().getProperties()
  const url = `https://api.pipedrive.com/v1/products?api_token=${env.PIPEDRIVE}`

  const config = {
    method: 'post',
    headers: {
      'Content-Type': 'application/json',
    },
    payload: JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url,config)
  const resData = JSON.parse(response)
  console.log(`Created item: ${data.code} | HTTP: ${response.getResponseCode()}`)

  updateCrmItemWithPipedriveId({crm: data.code, pipedrive: resData.data.id})
}

function updateItemInPipedrive(data,id) {
  const env = PropertiesService.getScriptProperties().getProperties()
  const url = `https://api.pipedrive.com/v1/products/${id}?api_token=${env.PIPEDRIVE}`

  const config = {
    method: 'put',
    headers: {
      'Content-Type': 'application/json',
    },
    payload: JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url,config)
  const resData = JSON.parse(response)
  console.log(`Updated item: ${data.code} | HTTP: ${response.getResponseCode()}`)

}