使用应用程序脚本将数据从一个Google工作表发送到另一Google工作表 [英] Send data from one google sheet to a another google sheet with app script

问题描述

我想知道你们中的任何一个成员是否能够帮助我重写下面的代码，该代码当前从名为"Sheet1"的标签发送数据.(这是我的提交表格)到数据库"标签中都在一个工作表中.以便代码现在将提交表单数据发送到仅包含数据库"列表的单独工作表中.标签.以下所有功能均分配给相应的按钮.

I wonder if any of you kind members would be able to help me rewrite the below code, which currently sends the data from the tab called "Sheet1" ( which is my submission form) to the tab "Database" all within one worksheet. So that the code now will send the submission form data to a separate worksheet containing only the "Database" tab. All below functions are assigned to the respective buttons.

 function ClearCell() {
 var ss        = SpreadsheetApp.getActiveSpreadsheet();
 var forms   = ss.getSheetByName("Sheet1");

var rangesToClear = ["B1" , "C2" , "C3" , "C4" , "C5" , "C6" , "C7" , "B14" , "B15" , "B16" , "B17" , 
"B18" , "B19" , "D14" , "D15" , "D16" , "D17" , "D18" , "D19" ];
 for (var i=0; i<rangesToClear.length; i++) {
 forms.getRange(rangesToClear[i]).clearContent();
}
}

 function SubmitData() {
 var ss        = SpreadsheetApp.getActiveSpreadsheet();
 var forms     = ss.getSheetByName("Sheet1");
 var datas     = ss.getSheetByName("Database");

 var values = [[forms.getRange("C2").getValue(),
             forms.getRange("C3").getValue(),
             forms.getRange("C4").getValue(),
             forms.getRange("C5").getValue(),
             forms.getRange("C6").getValue(),
             forms.getRange("C7").getValue(),
             forms.getRange("C8").getValue(),
             forms.getRange("B14").getValue(),
             forms.getRange("B15").getValue(),
             forms.getRange("B16").getValue(),
             forms.getRange("B17").getValue(),
             forms.getRange("B18").getValue(),
             forms.getRange("B19").getValue(),
             forms.getRange("D14").getValue(),
             forms.getRange("D15").getValue(),
             forms.getRange("D16").getValue(),
             forms.getRange("D17").getValue(),
             forms.getRange("D18").getValue(),
             forms.getRange("D19").getValue()]];
  datas.getRange(datas.getLastRow()+1,1,1,19).setValues(values);
  ClearCell();
  }

 var SEARCH_COL_INDX = 0;
 function Search() {

 var ss        = SpreadsheetApp.getActiveSpreadsheet();
 var forms    = ss.getSheetByName("Sheet1");

 var str  = forms.getRange("B1").getValue();
 var values = ss.getSheetByName("Database").getDataRange().getValues();
 for (var i = 0; i< values.length; i++) {
 var row = values[i];
 if (row[SEARCH_COL_INDX] == str) {

  forms.getRange("C2").setValue(row[19]);
  forms.getRange("C3").setValue(row[1]);
  forms.getRange("C4").setValue(row[2]);
  forms.getRange("C5").setValue(row[3]);
  forms.getRange("C6").setValue(row[4]);
  forms.getRange("C7").setValue(row[5]);
  forms.getRange("C8").setValue(row[6]);
  forms.getRange("B14").setValue(row[7]);
  forms.getRange("B15").setValue(row[8]);
  forms.getRange("B16").setValue(row[9]);
  forms.getRange("B17").setValue(row[10]);
  forms.getRange("B18").setValue(row[11]);
  forms.getRange("B19").setValue(row[12]);
  forms.getRange("D14").setValue(row[13]);
  forms.getRange("D15").setValue(row[14]);
  forms.getRange("D16").setValue(row[15]);
  forms.getRange("D17").setValue(row[16]);
  forms.getRange("D18").setValue(row[17]);
  forms.getRange("D19").setValue(row[18]);

  }
 }
 }

 var str  = forms.getRange("B1").getValue();
 var values = ss.getSheetByName("Data").getDataRange().getValues();
 for (var i = 0; i< values.length; i++) {
 var row = values[i];
 if (row[SEARCH_COL_INDX] == str) {
  var INT_R = i+1

   var values1 = [[forms.getRange("C2").getValue(),
             forms.getRange("C3").getValue(),
             forms.getRange("C4").getValue(),
             forms.getRange("C5").getValue(),
             forms.getRange("C6").getValue(),
             forms.getRange("C7").getValue(),
             forms.getRange("C8").getValue(),
             forms.getRange("B14").getValue(),
             forms.getRange("B15").getValue(),
             forms.getRange("B16").getValue(),
             forms.getRange("B17").getValue(),
             forms.getRange("B18").getValue(),
             forms.getRange("B19").getValue(),
             forms.getRange("D14").getValue(),
             forms.getRange("D15").getValue(),
             forms.getRange("D16").getValue(),
             forms.getRange("D17").getValue(),
             forms.getRange("D18").getValue(),
             forms.getRange("D19").getValue()]];

  datas.getRange(INT_R,1,1,19).setValues(values1);
  SpreadsheetApp.getUi().alert('well done ');
  ClearCell();
  }
 }
 }

推荐答案

您可以使用 SpreadsheetApp.openById('ID_GOES HERE');

供参考，您可能更喜欢以下链接 https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)

for reference, you may prefer this link https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)

function SubmitData() {
 var ss        = SpreadsheetApp.getActiveSpreadsheet();
 var forms     = ss.getSheetByName("Sheet1");
 var ss_new    = SpreadsheetApp.openById('ID_GOES HERE');
 var datas     = ss_new.getSheetByName("Database");

......

这篇关于使用应用程序脚本将数据从一个Google工作表发送到另一Google工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！