在Google工作表中从pipedrive API导入数据。如何将数据复制到正确的列和行中的工作表中 [英] Importing data from pipedrive API in Google sheets. How to copy the data into the sheet in the correct columns and rows

问题描述

我正在尝试导入所有 Pipedrive 交易，并在Google工作表中撰写我需要的信息。

我已经能够做到：访问feed并将数据解析为一个变量。
并将此变量打印到1个单元格中。

代码有点清理（在我学习的时候编译）

< preclass =lang-js prettyprint-override> //调用电子表格和活动页面的标准函数
function alldeals（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（） ;
var sheets = ss.getSheets（）;
var sheet = ss.getActiveSheet（）;

//下一步构建url的方式是在结束之间进行迭代，因为api只允许固定数量的调用（100），这样我可以慢慢填充表单。
var url =https://api.pipedrive.com/v1/deals?start=;
var url2 =& limit =
var start = 0;
var end = start + 50;
var token =& api_token = hiddenforobviousreasons


//调用api并用jsonparse填充dataAll。
//然后将信息设置到
// dataSet中，以便我可以用新数据重新填充数据。

var response = UrlFetchApp.fetch（url + start + url2 + end + token）;
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.id，data.value，data.pipeline_id]）; //你的JSON实体在这里


dataRange = sheet.getRange（1，1，rows.length，3）; // 3表示实体的总数
dataRange.setValues（rows）;




我得到 sheet.getRange 。

我想要实现的是将数据放入列id，value，pipeline_id

任何指向我需要去解决这个问题的方向都很棒！修复会很好，但一些指针会对我的理解更有用。

我得到的错误如下：

< p>          （regel 29，bestand'dealpull5.0'）

松散转换：

大小的坐标覆盖范围无效（line29，文件dealpull5.0）

解决方案

您从Web API检索到数据，将它转换成一个2维数组（每行都是一个单元格数组）。为了证实这一点，您可以在修改数据的循环之后添加Logger调用： b


$ b

  var rows = []，data; 
 
 for（i = 0; i< dataSet.length; i ++）{
 data = dataSet [i]; 
 rows.push（[data.id，data.value，data.pipeline_id]）; // JSON实体在这里
 
 
 Logger.log（JSON.stringify（rows，null，2））; //记录已转换的数据
  

如果你运行它，你应该在日志中看到类似这样的内容： / p>

  [ -  timestamp--] [
 [id1，value1，pipeline_id1]， 
 [id2，value2，pipeline_id2]，
 ... 
] 
  

接下来，您要将它写入电子表格中，如下所示：
$ b

  ABC 
 1 | ID |值| Pipeline_Id | 
 + ------ + ------- + ------------- + 
 2 | | | | 
 3 | | | | 
  

错误信息是抱怨数据的大小和大小不匹配。 / p>

注意事项：

• 由于有列标题，将包含来自API调用的数据 A2 ，它也表示为 R2C1 。


• 使用 Range.setValues（）时，所提供数据的所有行必须与范围的宽度相同。如果需要，您可能需要在调用 Range.setValues（）之前填充或修剪数据。
  

• 方便将范围大小与数据大小相匹配;这样，您可以轻松地适应从API检索的数据中的更改。

  
  $ b

    var dataRange = sheet.getRange（2，1，rows.length，rows [0] .length）; 
   dataRange.setValues（rows）; 
    

I am trying to import all Pipedrive deals and writing the information I need in Google sheets.

What I am already able to do: Access the feed and parse the data to a variable. And print this variable into 1 cell. Of course this isn't workable yet.

The code is somewhat scavenged (building while I'm learning)

// Standard functions to call the spreadsheet sheet and activesheet
function alldeals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

   //the way the url is build next step is to itterate between the end because api only allows a fixed number of calls (100) this way i can slowly fill the sheet.
  var url = "https://api.pipedrive.com/v1/deals?start=";
  var url2 = "&limit="
  var start = 0;
  var end = start+50;
  var token  = "&api_token=hiddenforobviousreasons"


  //call the api and fill dataAll with the jsonparse. 
//then the information is set into the 
//dataSet so that i can refill datall with new data.

  var response = UrlFetchApp.fetch(url+start+url2+end+token); 
  var dataAll = JSON.parse(response.getContentText()); 
  var dataSet = dataAll;


  //create array where the data should be put
  var rows = [], data;

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.id, data.value,data.pipeline_id]); //your JSON entities here
  }

  dataRange = sheet.getRange(1, 1, rows.length, 3); // 3 Denotes total number of entites
  dataRange.setValues(rows);

}

The error I am getting on the sheet.getRange.

What I want to achieve is put the data in the columns id, value, pipeline_id

Any pointers to what direction I need to go to solve this problem would be awesome! A fix would be nice to but some pointers would be more useful for my understanding.

The error i am getting is the following:

De coördinaten of afmetingen van het bereik zijn ongeldig. (regel 29, bestand 'dealpull5.0')

Loosely translated:

The coordinates of the size of the reach are invalid (line29,file "dealpull5.0")

解决方案

You've retrieved data from a web API, and transformed it into a 2-dimensional array (an array of rows, where each row is an array of cells). To confirm this, you can add a Logger call after the loop that grooms the data:

  var rows = [], data;

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.id, data.value,data.pipeline_id]); //your JSON entities here
  }

  Logger.log( JSON.stringify(rows,null,2) );   // Log transformed data

If you run that, you should see something like this in your logs:

[--timestamp--] [ 
   [ id1, value1, pipeline_id1 ],
   [ id2, value2, pipeline_id2 ],
   ...
]

Next, you want to write it into a spreadsheet, which looks like this:

     A       B         C
1 | ID   | Value | Pipeline_Id |
  +------+-------+-------------+
2 |      |       |             |
3 |      |       |             |

The error message is complaining that the size of the range and size of your data do not match.

Things to note:

• Since there are column headers, the first cell that will contain data from the API call will be A2, which is also expressed as R2C1.
• When using Range.setValues(), all rows of the provided data must be the same length as the width of the range. If necessary, you may need to pad or trim the data before calling Range.setValues().

• It is convenient to match the range size to the size of the data; that way, you can easily adapt to changes in the data retrieved from the API.

  var dataRange = sheet.getRange(2, 1, rows.length, rows[0].length);
  dataRange.setValues(rows);
  

这篇关于在Google工作表中从pipedrive API导入数据。如何将数据复制到正确的列和行中的工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！