Paypal API Question posted in
The official documentation to get you started can be found here.

Paypal api – Exception: The number of rows in the range must be at least 1

I am having trouble writing my json data to a google sheet – I get the exception "The number of rows in the data does not match the number of rows in the range."

I think the problem is with how the range is set – but I don’t know how to fix it. I include a sample of the json data after this script:

  function getApi() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var url = "https://www.lexology.com/api/v1/track/clients/articles?companyId=1013419&limit=100";
  var params = {
         "contentType": "application/json",
         "headers":{"ApiKey": "MYAPIKEY"
                    },

         };

var response = UrlFetchApp.fetch(url, params);
var dataAll = JSON.parse(response.getContentText()); 
var dataSet = dataAll;
var rows = [],
data;
  

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.companyID, data.companyName, data.articles ]); 
  }
 Logger.log(dataSet)

  dataRange = sheet.getRange(3, 1, rows.length, 3); 
  dataRange.setValues(rows);
}

The json data looks like this:

{
  "companyId": 1013419,
  "companyName": "Cisco Systems Inc",
  "jurisdictions": [],
  "workAreas": [],
  "tags": [],
  "fromDate": "2021-01-10T00:00:00+00:00",
  "toDate": "2021-02-10T00:00:00+00:00",
  "articles": [
    {
      "date": "2021-01-04T00:00:00",
      "articleUrl": "https://www.lexology.com/library/detail.aspx?g=253372ab-8cce-47c9-853f-4a9953becc0d",
      "title": "USPTO and IMPI Announce Parallel Patent Grant Initiative",
      "summary": "Under the terms of the MoU, a parallel patent grant framework was instituted which allowed IMPI to leverage search and examination results from the USPTO when issuing a counterpart Mexican patent.",
      "firmName": "Casimir Jones SC",
      "topic": null,
      "jurisdictions": [
        "Mexico",
        "USA"
      ],
      "workAreas": [
        "Patents"
      ],
      "reads": 3,
      "prints": 0,
      "authorNameOrPhotoClick": 0,
      "articleContentLink": 0,
      "forwardedUsingMailTo": 0,
      "viewOriginal": 0
    },
    {
      "date": "2020-12-23T00:00:00",
      "articleUrl": "https://www.lexology.com/library/detail.aspx?g=2c23065f-8479-4343-a889-2a072465bedc",
      "title": "Why Everyone Is Patenting Software Inventions",
      "summary": "Finding the correct balance requires the assistance of a patent strategist with knowledge of what the patent examiner is likely to accept, and who is skilled in crafting the patent in a way that covers what competitors will need in order to compete.",
      "firmName": "Finnegan, Henderson, Farabow, Garrett & Dunner, LLP",
      "topic": null,
      "jurisdictions": [
        "USA"
      ],
      "workAreas": [
        "IT & Data Protection",
        "Patents"
      ],
      "reads": 3,
      "prints": 0,
      "authorNameOrPhotoClick": 0,
      "articleContentLink": 0,
      "forwardedUsingMailTo": 0,
      "viewOriginal": 0
    }

…………EDIT………

I have fixed the typos in the code as pointed out, thank you.

The output I am looking for is set out in this sheet:

https://docs.google.com/spreadsheets/d/1c3_7D7KOwzR6mPNhKuCoGMNXODr9zgQG7qLzmpFfq6E/edit#gid=0&range=A1:F16

ALSO – when I do Logger.log(dataAll) it is not empty. I get the following:

12:58:42 AM Info    Logging output too large. Truncating output. {workAreas=[], articles=[{jurisdictions=[Mexico, USA], forwardedUsingMailTo=0.0, topic=null, prints=0.0, date=2021-01-04T00:00:00, authorNameOrPhotoClick=0.0, viewOriginal=0.0, workAreas=[Patents], title=USPTO and IMPI Announce Parallel Patent Grant Initiative, firmName=Casimir Jones SC, summary=Under the terms of the MoU, a parallel patent grant framework was instituted which allowed IMPI to leverage search and examination results from the USPTO when issuing a counterpart Mexican patent., reads=3.0, articleContentLink=0.0, articleUrl=https://www.lexology.com/library/detail.aspx?g=253372ab-8cce-47c9-853f-4a9953becc0d}, {articleUrl=https://www.lexology.com/library/detail.aspx?g=2c23065f-8479-4343-a889-2a072465bedc, authorNameOrPhotoClick=0.0, prints=0.0, jurisdictions=[USA], viewOriginal=0.0, topic=null, date=2020-12-23T00:00:00, reads=3.0, firmName=Finnegan, Henderson, Farabow, Garrett & Dunner, LLP, forwardedUsingMailTo=0.0, articleContentLink=0.0, summary=Finding the correct balance requires the assistance of a patent strategist with knowledge of what the patent examiner is likely to accept, and who is skilled in crafting the patent in a way that covers what competitors will need in order to compete., title=Why Everyone Is Patenting Software Inventions, workAreas=[IT & Data Protection, Patents]}, {date=2021-01-12T00:00:00, prints=0.0, reads=3.0, title=Election Ballot Verification - A Patent Subject Matter Eligibility Analysis, summary=v. CLS Bank Int., the court (1) determined that the claims were drawn to an abstract idea of “voting, verifying the vote, and submitting the vote for tabulation” and (2) found “no inventive concept in the claims sufficient to transform them into patent-eligible subject matter.” Recited at a high level of abstraction, the claims at issue before the court included one or more voting stations, each including a computer, a display, a printer, an input device, a ballot scanning machine, and a means for tabulating printed ballots., workAreas=[Litigation, Patents], jurisdictions=[USA], authorNameOrPhotoClick=0.0, forwardedUsingMailTo=0.0, topic=null, viewOriginal=0.0, articleContentLink=0.0, articleUrl=https://www.lexology.com/library/detail.aspx?g=544fc695-b8eb-4951-a19e-c2df4b7039b6, firmName=Baker & Hostetler LLP}, {authorNameOrPhotoClick=0.0, topic=null, forwardedUsingMailTo=0.0, date=2021-02-04T00:00:00, reads=3.0, articleUrl=https://www.lexology.com/library/detail.aspx?g=64b1aa84-d37f-4e80-9805-3509b48c1824, workAreas=[IT & Data Protection, Litigation], jurisdictions=[USA], title=Successful Dismissal of PayPal Class Action Over Breach Disclosures Serves as Risks Reminder, summary=Companies should consult closely with counsel when making a public announcement regarding a potential or confirmed data security incident to ensure they are thinking through the potential regulatory and litigation risks, whether a trading blackout period is appropriate during the period of investigation, and whether existing cybersecurity risk disclosures in the company’s public filings should be amended., firmName=Sheppard Mullin Richter & Hampton LLP, articleContentLink=0.0, prints=0.0, viewOriginal=0.0}, {topic=null, firmName=Wilmer Cutler Pickering Hale and Dorr LLP, articleUrl=https://www.lexology.com/library/detail.aspx?g=6cad7e90-ff4a-413f-9a4d-079a23c12a44, prints=0.0, reads=3.0, date=2020-12-22T00:00:00, title=Quick Takeaways: SolarWinds Cybersecurity Incident, viewOriginal=0.0, authorNameOrPhotoClick=0.0, articleContentLink=0.0, forwardedUsingMailTo=0.0, workAreas=[IT & Data Protection], summary=Certain versions of SolarWinds software updates that assist organizations in managing their computer networks were compromised by what some officials, such as the US Secretary of State and the US Attorney General, have indicated is a Russian intelligence agency., jurisdictions=[USA]}, {firmName=Reed Smith LLP, forwardedUsingMailTo=0.0, authorNameOrPhotoClick=0.0, summary=The Guidelines set out the law applicable to operations consisting in reading or writing data on users’ devices and clarify the CNIL’s approach, while the Recommendation aims to guide organisations in their compliance efforts by providing examples of practical methods that can be used, notably to collect consent., date=2021-01-22T00:00:00, title=Adtech regulatory round-up January 2021, articleUrl=https://www.lexology.com/library/detail.aspx?g=8973269a-306a-48d3-9b19-0e50a6647d96, articleContentLink=0.0, viewOriginal=1.0, jurisdictions=[Germany, France, United Kingdom, USA], topic=null, workAreas=[IT & Data Protection, Litigation], reads=3.0, prints=0.0}, {firmName=Sheppard Mullin Richter & Hampton LLP, reads=3.0, articleUrl=https://www.lexology.com/library/detail.aspx?g=8e3a8f46-982e-41ba-a4bc-9e8af57ac7db, forwardedUsingMailTo=0.0, summary=The second carve-out from the definition of protected information was for information known to the employee before he joined TGS, “as evidenced by Employee’s written records.” The Brown court took a particularly dim view of this exception, because it would mean that information which, by definition was not confidential before Brown began working at TSG, would become so unless Brown had written records proving he knew of it beforehand., authorNameOrPhotoClick=0.0, title=California Court Strikes Down Overbroad Confidentiality Agreement as a de facto Non-Compete, articleContentLink=0.0, prints=0.0, topic=null, viewOriginal=0.0, date=2021-01-05T00:00:00, workAreas=[Employment & Labor, Litigation], jurisdictions=[USA]},
Tags:

2

Answers


  1. 0

    Issue:

    Apparently you can’t get a range with 0 rows. In this method getRange(row, column, numRows, numColumns), every parameter should be a number larger than 0.

    Why is rows.length 0?

    The issue has to do with the fact that your JSON dataSet is not an array, therefore the for loop is terminated because dataSet.length is undefined. As a result row.length is zero because no values were added.

    Updated Solution:

    Assuming the first row (headers) in your sheet is:

    companyId companyName articles.date articles.articleUrl articles.title articles.summary

    function getApi() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const url = "https://www.lexology.com/api/v1/track/clients/articles?companyId=1013419&limit=100";
  const params = {
         "contentType": "application/json",
         "headers":{"ApiKey": "MYAPIKEY"
                    },

         };
         
  const response = UrlFetchApp.fetch(url, params);
  const dataSet = JSON.parse(response.getContentText()); 

  const data = 
              dataSet.articles.map((r,i)=>
              i==0?[dataSet.companyId,dataSet.companyName,r.date,r.articleUrl,r.title,r.summary]:
                   ["","",r.date,r.articleUrl,r.title,r.summary]
              );
  sheet.getRange(sheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}
    Login or Signup to reply.
  2. 0

    The simplest solution is to move the line from fig1the line under the first row to fig2the line under the second row. It works for me!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search