2014年1月24日 星期五

google試算表跨檔匯入資料

試過以內建的函數來寫,但因為都還在研發未開放(XD),目前似乎無解,所以嘗試用script寫了跨檔匯入的功能 範例檔請自行下載,共有三個檔 跨工作表查詢-匯入2個外部檔(含script)、北部銷貨、中部銷貨,請用副本模式下載至自己的雲端硬碟
執行範例檔之前,並至 跨工作表查詢-匯入2個外部檔 / 工作簿KEY工作表 修正成你的副本Key(副本的KEY會跟作者的KEY不一樣)






script內容如下:
//宣告全域變數,讓其他自建函數也能取用該值
var keys
var thisbookkey
var values

function onOpen() {
  //目前新版的試算表不提供於表格內圖案指定指令碼,所以用了選單功能
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "各地區彙總",
    functionName : "getOtherWorkbook"
  }];
  sheet.addMenu("銷售報表", entries);
};

function getOtherWorkbook(){
  getKey();//取得自建函數getKey()的 keys值
  // keys值要運用在迴圈,取得每個工作簿的內容
  var targetlastrow=1
  for (var i=0;i<keys.length;i++){
    
      var ss =SpreadsheetApp.openById(keys[i]);//指定工作簿
      SpreadsheetApp.setActiveSpreadsheet(ss);//指定工作表
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作表1");
      var lastrow=sheet.getLastRow();//用getLastColumn()會出錯,無所謂,因為一般資料庫表單欄位不會讓查詢使用者動
      if(i==0){
        values= sheet.getSheetValues(1, 1, lastrow, 3);//假設各地區銷售工作表的欄位數是固定的,這裡的範例是3欄
      }else{
        values= sheet.getSheetValues(2, 1, lastrow, 3);//第一個工作簿以後,不需要再取出欄位名稱
      }
    //Logger.log(values.length);
      copy2TargetWorkbook();
  }
}
 
function copy2TargetWorkbook(){
  //COPY至目的工作簿表格
  var ss = SpreadsheetApp.openById("1PSCFJ-XohhHnV2uiunYjljeFMgSSRPlHH5qHXuzDWr4");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("銷售紀錄彙總表");
  var lastrow=sheet.getLastRow();
  lastrow++;//
  //Logger.log(values);
  
  var cell=sheet.getRange(lastrow, 1, values.length, values[0].length);//指定目的儲存格範圍 
  cell.setValues(values);//要讓 資料自動展開,不可用cell.setValue(values),不然不會分解值,會全部放在第一個儲存格 
  //Logger.log(sheet.getLastRow());//由 檢視 / 記錄  查看執行的結果
  
}

function getKey(){
  thisbookkey=SpreadsheetApp.getActiveSpreadsheet().getId();//取得主工作簿的key
  var ss = SpreadsheetApp.openById(thisbookkey);
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("銷售紀錄彙總表");
  //先清空 銷售紀錄彙總表 的資料內容
  sheet.clearContents();
  //工作簿KEY工作表內有放各工作簿的Key值,取出各工作簿的KEY值
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作簿KEY");
  var lastrow=sheet.getLastRow();
  keys =sheet.getSheetValues(2, 2, lastrow-1, 1);
  //Logger.log(thisbookkey);
}

3 則留言:

  1. 請問使用openbyid或openbyurl都出現"您沒有執行該動作的權限"的話
    是需要設定什麼或需要變更什麼嗎?

    回覆刪除
    回覆
    1. 請問你有拷貝到你的雲端硬碟來執行嗎?
      你不能直接用我的檔案來執行喔,我只開放"檢視"的權限

      PS:近來很少上來維護,所以至今才回覆,先跟你說聲抱歉了

      刪除
  2. Kama Kama Online Casino, Poker and Casino Games
    Kama is a leading provider of quality online gaming. Enjoy our games and receive a 온카지노 사이트 Welcome Bonus and Free Spins. Play today!

    回覆刪除