いずれかのフィールド内に改行があるURLからCSVをインポートする

debugcn 投稿 Dev

ob1

CSVファイルをURLからGoogleスプレッドシートにインポートするスクリプトを作成しました。過去にUtilities.parseCsv（csv）を問題なく使用しました。ただし、この特定のCSVファイルには、複数行の戻り値（ALT + Enter）を持つフィールドが含まれています。これにより、解析されたファイルは、これらのフィールドに遭遇したときに余分な行を作成します。解析する前にCSVファイルをサニタイズするのが最善の方法だと思いますが、その方法がわかりません。

問題のフィールドは「説明」（列G）です。

現在のスクリプト：

function importCampaignReport() {
var csvUrl = "https://s3.amazonaws.com/redacted.csv";
var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
var csvData = Utilities.parseCsv(csvContent);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Perm URL Campaign Dashboard Report');
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

サンプルcsvContent：

"Campaign Status","Billing Status",ID,Mon,Customer,Campaign,Description,"Customer Type",CID,CPL,Archived,"Start date","End date","Sales rep","Total Days Remaining","Monthly Days Left",Pace,"Month Delivered","Month Accepted","Month Remaining","% Delivered","% Accepted","Campaign Goal","Campaign Delivered","Campaign Remaining","% Campaign delivered","Campaign Undelivered","Campaign Awaiting Verification","Campaign Unverified","% Campaign Possible”,”CL Goal”,”CL Delivered”,”CL Remaining”,”CL Undelivered”,”CL Awaiting Verification”,”CL Unverified","#1 Partner Code","#1 Partner Goal","#1 Partner Cost","#2 Partner Code","#2 Partner Goal","#2 Partner Cost","#3 Partner Code","#3 Partner Goal","#3 Partner Cost”,”CL Mon Delivered","#1 Partner Mon Delivered","#2 Partner Mon Delivered","#3 Partner Mon Delivered","#4 Partner Mon Delivered","#5 Partner Mon Delivered","#6 Partner Mon Delivered","#4 Partner Code","#4 Partner Goal","#4 Partner Cost","#5 Partner Code","#5 Partner Goal","#5 Partner Cost","#6 Partner Code","#6 Partner Goal","#6 Partner Cost"
Completed,Pending,4607,"Mar 2018”,XYZ,”Big Software","IT Staff+, 500+, UK, France, Netherlands, CQs",1,,27.50,no,03/01/2018,03/24/2018,RD,0,0,87,97,87,0,111,89,87,97,0,111,0,0,0,111,0,2,0,0,0,0,STW001,87,13.00,,,,,,,2,95,,,,,,,,,,,,,,
Completed,Pending,4938,"Jan 2018",TestCustomer,TestCampaign,"Description Line 1
Description Line 2
Description Line 3",1,,0.00,no,04/09/2017,04/09/2018,RD,0,0,1,0,0,1,0,0,10,0,10,0,0,0,2,20,0,0,0,0,0,0,LC,10,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Completed,Pending,4938,"Feb 2018",TestCustomer,TestCampaign,"Description Line 1
Description Line 2
Description Line 3",1,,0.00,no,04/09/2017,04/09/2018,RD,0,0,1,0,0,1,0,0,10,0,10,0,0,0,2,20,0,0,0,0,0,0,LC,10,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Completed,Pending,4938,"Mar 2018",TestCustomer,TestCampaign,"Description Line 1
Description Line 2
Description Line 3",1,,0.00,no,04/09/2017,04/09/2018,RD,0,0,1,0,0,1,0,0,10,0,10,0,0,0,2,20,0,0,0,0,0,0,LC,10,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Completed,Pending,4938,"Apr 2018",TestCustomer,TestCampaign,"Description Line 1
Description Line 2
Description Line 3",1,,0.00,no,04/09/2017,04/09/2018,RD,0,0,1,0,0,1,0,0,10,0,10,0,0,0,2,20,0,0,0,0,0,0,LC,10,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Active,Pending,4944,"Jan 2018",TestCustomer1,Aberdeen,"Description Line 1
Description Line 2",1,,0.00,no,04/24/2017,04/24/2018,MN,0,0,9,0,0,9,0,0,100,0,100,0,0,1,9,10,0,0,0,0,0,0,ABD,100,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Active,Pending,4944,"Feb 2018",TestCustomer1,Aberdeen,"Description Line 1
Description Line 2",1,,0.00,no,04/24/2017,04/24/2018,MN,0,0,8,0,0,8,0,0,100,0,100,0,0,1,9,10,0,0,0,0,0,0,ABD,100,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Active,Pending,4944,"Mar 2018",TestCustomer1,Aberdeen,"Description Line 1
Description Line 2",1,,0.00,no,04/24/2017,04/24/2018,MN,0,0,9,0,0,9,0,0,100,0,100,0,0,1,9,10,0,0,0,0,0,0,ABD,100,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Active,Pending,4944,"Apr 2018",TestCustomer1,Aberdeen,"Description Line 1
Description Line 2",1,,0.00,no,04/24/2017,04/24/2018,MN,0,0,7,0,0,7,0,0,100,0,100,0,0,1,9,10,0,0,0,0,0,0,ABD,100,0.00,,,,,,,0,0,,,,,,,,,,,,,,
Active,Pending,5035,"Jan 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,254,0,0,254,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Feb 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,229,0,0,229,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Mar 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,254,0,0,254,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Apr 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,246,0,0,246,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"May 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,254,0,0,254,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Jun 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,246,0,0,246,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Jul 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,254,0,0,254,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Aug 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,254,0,0,254,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Sep 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,247,0,0,247,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Oct 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Nov 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,247,0,0,247,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Dec 2018",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Jan 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Feb 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,230,0,0,230,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Mar 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Apr 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,247,0,0,247,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"May 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Jun 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,247,0,0,247,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Jul 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,0,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Aug 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,12,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Sep 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,30,247,0,0,247,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,
Active,Pending,5035,"Oct 2019",TestCustomer,TestCampaign,"Description Line 1",1,,0.00,no,05/04/2017,05/04/2020,MN,259,31,255,0,0,255,0,0,9002,0,9002,0,0,0,0,0,9000,0,9000,0,0,0,Redacted,1,0.00,B2M001,1,0.00,,,,0,0,0,,,,,,,,,,,,,

タナイケ

CSVデータをスプレッドシートに入れたいと考えています。
CSVデータには、改行のある値が含まれています。
- 改行のある値は二重引用符で囲まれています。
- このような値は、列「G」で確認できます。
改行を含む値を入力します。
これは、Google AppsScriptを使用して実現したいと考えています。

私の理解が正しければ、この答えはどうですか？これは、いくつかの答えの1つにすぎないと考えてください。

問題：

残念ながら、現段階Utilities.parseCsv()では、改行を含むセル値を持つデータには直接使用できないようです。この問題を回避するために、以下の2つのパターンを提案したいと思います。

パターン1：

このパターンでは、次のフローが使用されます。

フロー：

データをで解析するためにUtilities.parseCsv()、最初に、二重引用符で囲まれた値が時間文字列に置き換えられます。
値はによって解析されUtilities.parseCsv()ます。
時間文字列を元の値に置き換えます。
データをスプレッドシートに入れます。

変更されたスクリプト：

スクリプトを変更する場合は、以下のように変更してください。

から：

var csvData = Utilities.parseCsv(csvContent);

に：

// The values enclosed by the double quotes are replaced by the temporal string.
var temp = [];
var tempStr = "###temp###";
var t = csvContent.replace(/\"[\w\s\S]+?\"/g, function(m) {
  temp.push(m.replace(/\"/g, ""));
  return tempStr;
});

// Parse data as CSV data.
var csvData = Utilities.parseCsv(t);

// eplace the temporal strings to the original values.
var h = 0;
csvData.forEach(function(e, i) {
  var j = e.indexOf(tempStr);
  if (j > -1) {
    csvData[i][j] = temp[h];
    h++;
  }
});

パターン2：

このパターンでは、次のフローが使用されます。

フロー：

CSVファイルをGoogleスプレッドシートに変換します。
- この変換されたスプレッドシートは、一時的なスプレッドシートとして使用されます。
変換されたスプレッドシートから値を取得します。
一時的なスプレッドシートを削除します。
値を宛先のスプレッドシートに入力します。

サンプルスクリプト：

このスクリプトを使用する前に、高度なGoogleサービスでドライブAPIを有効にしてください。

function importCampaignReport() {
  var csvUrl = "https://s3.amazonaws.com/redacted.csv";
  var blob = UrlFetchApp.fetch(csvUrl).getBlob(); // Modified


  // I added below script.
  var id = Drive.Files.insert({title: "temporalSpreadsheet", mimeType: MimeType.GOOGLE_SHEETS}, blob).id;
  var csvData = SpreadsheetApp.openById(id).getSheets()[0].getDataRange().getValues();
  Drive.Files.remove(id);


  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Perm URL Campaign Dashboard Report');
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

注意：

この変更されたスクリプトでは、共有CSVデータを使用してスクリプトが機能することを確認できました。
CSVファイルを手動でスプレッドシートにインポートすると、上記の変更したスクリプトと同じ結果であることが確認できました。

参照：

追加1：

Sheets APIを使用すると、URLから取得した値が直接解析され、スプレッドシートに配置されることがわかりました。そこでもう一つのパターンとして、これを提案したいと思います。サンプルスクリプトは次のとおりです。この場合、一時ファイルは使用されず、プロセスコストは上記の2つのパターンよりも低くなる可能性があります。

サンプルスクリプト：

このスクリプトを使用する前に、高度なGoogleサービスでSheetsAPIを有効にしてください。

function importCampaignReport() {
  var csvUrl = "https://s3.amazonaws.com/redacted.csv";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Perm URL Campaign Dashboard Report');
  var resource = {requests: [{pasteData: {data: csvContent, coordinate: {sheetId: sheet.getSheetId()}, delimiter: ","}}]};
  Sheets.Spreadsheets.batchUpdate(resource, spreadsheet.getId());
}

参照：

PasteDataRequest

追加2：

値がスプレッドシートに配置されたら、値から改行を削除する必要があります。

上記のように理解できました。私の理解が正しければ、次のサンプルスクリプトはどうですか？

このスクリプトを使用する前に、高度なGoogleサービスでSheetsAPIを有効にしてください。

サンプルスクリプト：

function importCampaignReport() {
  var csvUrl = "https://s3.amazonaws.com/redacted.csv";
  var data = UrlFetchApp.fetch(csvUrl).getContentText();

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Perm URL Campaign Dashboard Report');
  var sheetId = sheet.getSheetId();
  var resource = {requests: [
    {pasteData: {data: data, coordinate: {sheetId: sheetId}, delimiter: ","}},
    {findReplace: {range: {sheetId: sheetId, startColumnIndex: 6, endColumnIndex: 7}, find: "\n", replacement: " ", searchByRegex: true}}
  ]};
  Sheets.Spreadsheets.batchUpdate(resource, spreadsheet.getId());
}

あなたのコメントでは、についてthe 5th column (G)、5列目は「E」列だと思います。しかし、あなたは列「G」を言います。私はこれについて混乱します。したがって、現在のスクリプトでは、列「G」を処理できます。列「E」に対してこれを実行する場合は、を設定してくださいstartColumnIndex: 4, endColumnIndex: 5。
エラーが発生した場合は、問題を再現するためのスクリプトを提供してください。これで確認したいと思います。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-06-11

コメントを追加

サインイン

分類Dev

Related 関連記事

記事