2023/12/22

VBA / 使用Excel VBA批次寄信 2

VBA / 使用Excel VBA批次寄信 是使用 CDO.Message物件 
 但其實也可以透過 Outlook.Application 來使用 Outlook功能 
 引用 Outlook.Application 建立 olMailItem 之後 
 後續的程式碼基本上 跟 VBA / 在Outlook使用VBA批次寄信  VBA / 在Outlook使用VBA批次寄信2 是相同的

excel工作表的格式

整體程式碼

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
Public Sub SendMail()
    Dim xOutApp As Object
    Dim xMailItem As Object
    
    Dim xLastRow As Long
    
    Dim erMsg As String '紀錄錯誤訊息
    Dim erNm As Integer '紀錄錯誤訊息筆數

    xLastRow = Sheets("mail").Range("B200").End(xlUp).Row
    
    '計數器 記錄實際寄出的郵件數量
    Dim send_Num As Integer
    send_Num = 0
    
    Set xOutApp = CreateObject("Outlook.Application")
    If xLastRow <> 1 Then
        For n = 2 To xLastRow
            Dim check_send As String '是否郵寄 字串形式
            check_send = Sheets("mail").Range("A" & n).Value
        
            If check_send <> "" Then
            
                    Dim delaysec As Integer
                    Dim SendDate As Date
                    
                    delaysec = Int((5 - 2 + 1) * Rnd() + 2)
                    SendDate = Now()
                    
        '            Debug.Print SendDate
                    
                    SendDate = DateAdd("s", delaysec, SendDate)
                    
        '            Debug.Print SendDate
                    
                    Set xMailItem = xOutApp.CreateItem(olMailItem)
                    
                    '內文格式
                    If Sheets("mail").Range("B" & n).Value = "txt" Then
                        With xMailItem
                            .Subject = Sheets("mail").Range("F" & n).Value '主旨
                            .To = Sheets("mail").Range("D" & n).Value      '收件人
                            .BodyFormat = olFormatPlain                    '內文編碼格式
                            .Body = Sheets("mail").Range("G" & n).Value    '內文
                        End With
                        
                    ElseIf Sheets("mail").Range("B" & n).Value = "html" Then
                    
                        With xMailItem
                            .Subject = Sheets("mail").Range("F" & n).Value  '主旨
                            .To = Sheets("mail").Range("D" & n).Value       '收件人
                            .BodyFormat = olFormatHTML                      '內文編碼格式
                            .HTMLBody = Sheets("mail").Range("G" & n).Value '內文
                        End With
                        
                    Else
                    
                        MsgBox "請確認內文編碼格式"
                        
                    End If
                    
                    '附加檔案1
                    If Sheets("mail").Range("H" & n).Value <> "" Then
                        xMailItem.Attachments.Add Sheets("mail").Range("H" & n).Value
                    End If
                    
                    '附加檔案2
                    If Sheets("mail").Range("I" & n).Value <> "" Then
                        xMailItem.Attachments.Add Sheets("mail").Range("I" & n).Value
                    End If
                      
                    'cc收件人
                    If Sheets("mail").Range("E" & n).Value <> "" Then
                        mail.CC = Sheets("mail").Range("E" & n).Value
                    End If
                    
                    'DeferredDeliveryTime 設定郵件的傳送日期及時間
                    xMailItem.DeferredDeliveryTime = SendDate
                    Debug.Print "編號：" & (n - 1) & "信件，寄出時間：" & SendDate
                    
        '               發生錯誤仍繼續執行
                    On Error Resume Next
                            
        '               當發生錯誤時 用 erMsg erNm 紀錄
                    If Err.Number <> 0 Then
                        erMsg = erMsg & "編號-" & (n - 1) & "-" & Err.Number & "/" & Err.Description & Chr(10)
                        erNm = erNm + 1
                    End If
                                
                    '寄出郵件
    '                xMailItem.Send
    
                    '顯示郵件視窗
                    xMailItem.Display
                    
                    Set xMailItem = Nothing
                    
                    '計數器+1
                    send_Num = send_Num + 1
            End If
        '       正常偵錯
                On Error GoTo 0
        Next
        
        '顯示錯誤的紀錄
        If erMsg <> "" Then
            Debug.Print erMsg
            'MsgBox erMsg
        End If
        
        Debug.Print "寄送完成，共寄出" & send_Num & "封，有" & erNm & "筆錯誤。"
            
        MsgBox "寄送完成，共寄出" & send_Num & "封，有" & erNm & "筆錯誤。"
    
    End If
    
    Set xOutApp = Nothing
    
End Sub
 

2023/12/06

VBA / 在Outlook使用VBA批次寄信 2

延續VBA / 在Outlook使用VBA批次寄信 修改了幾次程式碼，將整個流程更順暢一些

#18-44 檔案選取功能，透過對話窗選取要讀取的excel檔案

#124-145 間隔時間，利用Outlook.MailItem物件的DeferredDeliveryTime來設定

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
 
Public Sub sendMail8()
   ' Dim excelMail As Excel.Application                    '早期繫結
    Dim excelMail As Object                                '晚期繫結
    Dim mail As Outlook.MailItem
    Dim Data As String 'mail_list檔案路徑
    Dim r As Integer
    Dim n As Integer
    Dim e As String '內文編碼
    Dim t As String '收件者
    Dim s As String '主旨
    Dim b As String '內文
    Dim a As String '附件
    Dim erMsg As String '紀錄錯誤訊息
    Dim erNm As Integer '紀錄錯誤訊息筆數
    
'    Data = "C:\Users\edu\Desktop\mail_list.xlsx"
'    透過 Excel Application建立FileDialog
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = False
    
    Dim fd As Office.FileDialog
    Set fd = xlApp.Application.FileDialog(msoFileDialogFilePicker)
    
    ' 視窗標題
    fd.Title = "請選擇 mail_list.xlsx 檔案"
    
    ' 初始目錄
    fd.InitialFileName = "%USERPROFILE%\Desktop\mail_list.xlsx"
    
    '設定檔案類型
    fd.Filters.Clear
    fd.Filters.Add "試算表", "*.xls*", 1
'    Dim selectedItem As Variant
    
    If fd.Show = -1 Then
'        For Each selectedItem In fd.SelectedItems
'            Debug.Print selectedItem
        Data = fd.SelectedItems(1)
'        Next
    End If
    
    Set fd = Nothing
        xlApp.Quit
    Set xlApp = Nothing
    
    If Data <> "" Then
        
        MsgBox Data
        
       ' Set excelMail = New Excel.Application                 '早期繫結
        Set excelMail = CreateObject("excel.application")      '晚期繫結
        
        With excelMail
            .Visible = False
            .Workbooks.Open (Data)
        End With
        
        'MsgBox TypeName(excelMail)      'application
        r = excelMail.ActiveWorkbook.Sheets("mail").UsedRange.Rows.Count  '取得列數1
        
    '    r = excelMail.ActiveWorkbook.Sheets("mail").Range("A1").End(xlDown).Row  '取得列數2  引用excel library 不然即使是晚期繫節都會出現錯誤
    
    '    MsgBox r
        
        If r <> 1045678 Then
            For n = 2 To r
                If excelMail.ActiveWorkbook.Sheets("mail").Range("A" & n) <> "" Then  '路徑要完整 不然會出錯
                    e = excelMail.ActiveWorkbook.Sheets("mail").Range("B" & n).Value
                    t = excelMail.ActiveWorkbook.Sheets("mail").Range("D" & n).Value
                    c = excelMail.ActiveWorkbook.Sheets("mail").Range("E" & n).Value
                    s = excelMail.ActiveWorkbook.Sheets("mail").Range("F" & n).Value
                    b = excelMail.ActiveWorkbook.Sheets("mail").Range("G" & n).Value
                    a = excelMail.ActiveWorkbook.Sheets("mail").Range("H" & n).Value
                    
                    Debug.Print s
                    Debug.Print t
                    
                    Set mail = Application.CreateItem(olMailItem)
                    If e = "txt" Then
                        With mail
                            .To = t
                            .Subject = s
                            .BodyFormat = olFormatPlain
                            .Body = b
    '                        .Attachments.Add a
    '                        .Send
                        End With
                    
                    ElseIf e = "html" Then
                        With mail
                            .To = t
                            .Subject = s
                            .BodyFormat = olFormatHTML
                            .HTMLBody = b
    '                        .Attachments.Add a
    '                        .Send
                        End With
                    Else
                        
                        MsgBox "請確認內文編碼格式"
                    
                    End If
                    
                    If a <> "" Then
                        
                        mail.Attachments.Add a
                    
                    End If
                    
                    If c <> "" Then
                        mail.CC = c
                    End If
                    
    '               發生錯誤仍繼續執行
                    On Error Resume Next
                    
    '               當發生錯誤時 用 erMsg erNm 紀錄
                    If Err.Number <> 0 Then
                        erMsg = erMsg & "編號-" & n - 1 & "-" & Err.Number & "/" & Err.Description & Chr(10)
                        erNm = erNm + 1
                    End If
                    
                    '       間格時間(單位:秒) 2<= delaysec <= 5
                    '       int((數字上限 - 數字下限 + 1) * Rnd() + 數字下限)
                    delaysec1 = Int((5 - 2 + 1) * Rnd() + 2)
                    delaysec2 = Int((5 - 2 + 1) * Rnd() + 2)
                    delaysec3 = delaysec1 * 5 + delaysec2
                    
                    Debug.Print delaysec3
                    
'                    newHour = Hour(Now())
'                    newMinute = Minute(Now())
'                    newSecond = Second(Now()) + delaysec
'
'                    waitTime = TimeSerial(newHour, newMinute, newSecond)
'
'                    excelMail.Wait waitTime                                '在excel vba 為 Application.Wait
                    
                    SendDate = Now()
                    SendDate = DateAdd("s", delaysec3, SendDate)
                    
                    Debug.Print "Your mail will be sent at: " & SendDate
                    
                    mail.DeferredDeliveryTime = SendDate
                    
                    mail.Send
                    
                End If
                
                Set mail = Nothing
    
            Next
            
    '       正常偵錯
            On Error GoTo 0
        End If
        
        excelMail.Quit
        
        Set excelMail = Nothing
        
        '顯示錯誤的紀錄
        If erMsg <> "" Then
            Debug.Print erMsg
'            MsgBox erMsg
        End If
        
        Debug.Print "寄送完成，共寄出" & (r - 1) - erNm & "封，有" & erNm & "筆錯誤。"
        
        MsgBox "寄送完成，共寄出" & (r - 1) - erNm & "封，有" & erNm & "筆錯誤。"
     
     Else
     
        MsgBox "請重新執行，並選取 mail_list.xlsx"
        Exit Sub
        
     End If
     
End Sub
   

2023/11/16

Gg / 透過Google Apps Script隨機分配Google Forms問卷

這個是 Gg / 如何在Google Forms 隨機抽取題目(選擇題) 的再折衷方式 
原因除了隨機產生的問卷時間可能會很久 
表單資料的工作表會很長一大串，事後整理也不方便 
而且有沒有必要這樣隨機組合問題，也是一個需要考量的重點 
所以這次採取再折衷的方法 
這個方式是借鏡別人的模式 我不確定實際流程是否一樣 
但是概念跟目的應該是相似的 
也就是透過網頁點取"取得問卷連結"來隨機獲得事先建立的問卷

   

整體架構分為2個部分：Google Apps Scritp-程式部分、Google spreadsheets-題庫部分

A. Google Apps Scritp-程式部分

程式碼.gs

因為是部署成網路應用程式，所以必須使用 doGet()取得form.html的內容，這樣執行程式時才會有網頁內容 
include() 是為了讓 form.html可以讀取在後端的style.css.html 
getForm()配合form.html的按鈕的觸發事件執行 google.script.run.withSuccessHandler(showForm).getForm()，將資料回傳到form.html的 showForm() 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
function doGet(e) {
  var output = HtmlService.createTemplateFromFile("form").evaluate().setTitle("問卷系統");
  output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);//可以將發布成html介面的程式  崁入其他網站
  return output;
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getForm() {
  // https://docs.google.com/spreadsheets/d/********************************************/
  // sheet name 問卷表單
  var ss = SpreadsheetApp.openById("********************************************").getSheetByName("問卷表單"); 

  var Row = ss.getLastRow();
  //Logger.log('Row- '+Row);

  var g = ss.getRange("D2").getValue()+1;     //取得在D2儲存上一次的序號  +1=列號
  Logger.log("g-"+g);
  
  if(g !==""){
    //Math.floor(Math.random()*(max-min+1))+min
      var i = Math.floor(Math.random()*(Row-1+1))+1; // 本次序號
      Logger.log("本次序號 i-"+i);
      
      //如果跟D2一樣 則重新取得i
      while( g == i || i==1){
        i = Math.floor(Math.random()*(Row-1+1))+1;
        Logger.log("重新取號 i-"+i);
      }
      ss.getRange("D2").setValue(i-1);
  }else{
    //如果 D2是空值 則直接取值
    var i = Math.floor(Math.random()*(Row-1+1))+1; // 本次序號
    Logger.log("初始序號-"+i);
    ss.getRange("D2").setValue(i-1);
  }
  //回傳網址    
  var msg = '<h3><a href="' +ss.getRange("C"+i).getValue() +'" target="_blank">問卷連結</a></h3>'
  Logger.log(msg);

  return msg;

}
 

form.html

透過btnl綁定按鈕send的觸發事件來執行google.script.run.withSuccessHandler(showForm).getForm()，執行後端程式 getForm() 當 getForm()回傳資料時，由showForm()取得後端回傳資料，寫入div output之中 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>檔案上傳系統</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <base target="_top">
    <?!= include('style.css'); ?>
  </head>
  <body>
    <div class="container">
        <div class="row">
            <div class="col-8 mx-auto box">
                <h1 class="mb-5 text-center"><strong>取得隨機問卷</strong></h1>
                    <input type="submit" id="send" class="btn btn-primary btn-lg btn-block pt-2 pb-2" value="取得問卷連結">
                <div id="output"></div>
            </div>
        </div>
    </div>
  </body>
  <script>
  var btn1= document.getElementById("send");
  btn1.addEventListener("click", function(e){
      console.log("btn1");
      btn1.disabled = true;
      document.getElementById("output").innerHTML = "";
      document.getElementById("output").style.display='none';
      google.script.run.withSuccessHandler(showForm).getForm();
    }
  )
  //寫入網頁
  function showForm(form){    
    console.log(form);
    btn1.disabled = false;
    document.getElementById("output").innerHTML = "";
    document.getElementById("output").innerHTML = form;
    document.getElementById("output").style.display='block';
  }  
  </script>
</html>
 

style.css.html

設定整體的字形，以及div output的樣式 因為已經在form.html載入bootstrap來設定 所以也就沒有再特別設定什麼 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
<style>
    body{
        font-family: "Microsoft JhengHei";
    }
  #output{
    display:  none;
    text-align: center;
    padding:20px;
    border: 2px solid #FF0000;
    //width: 520px;
    height:100px;
  }

  .box{
    padding: 50px;
    margin-top: 50px;
    border: 1px solid #fff;
    border-radius: 30px;
    box-shadow: 0px 20px 50px 0px rgba(0,0,0,0.2);
  }
</style>
 

B. Google spreadsheets-題庫部分

基本結構與命名，必須跟程式碼.gs相互配合 D欄是為了記錄前一次的編號，配合程式來避免新取得的編號是重複的

後記

無論是作為問卷或者試卷，這樣的方式可能會比較理想 相對可以確保各表單內容的品質 事後整理也會比較方便

2023/11/02

Excel / VLOOKUP / LOOKUP函數 (2024/2/26 修改)

Excel 多個條件，透過VLOOKUP / LOOKUP函數尋找一個值

其實就是把多個條件合併成一個條件

例如：

條件1+條件2→社區+戶型，回傳符合條件的價格

使用VLOOKUP 函數

H2=VLOOKUP(F2&G2,CHOOSE({1,2},$A$2:$A$10&$B$2:$B$10,$C$2:$C$10),2,0)

先利用CHOOSE函數-CHOOSE(index_num, value1, [value2], …)

如果 index_num 是個陣列，評估 CHOOSE 時會評估陣列中的每個數值

白話來說就是會得到所有評估值的陣列結果，並且會依據 index_num來組合陣列

所以這個例子會得到條件1( 社區)、條件二(戶型)，與對應結果(價格)形成的一個參照範圍{條件1&條件2 , 對應結果}

這樣就可以在VLOOKUP作為查閱值的參照範圍，取得傳回值

使用LOOKUP 函數

I2=LOOKUP(1,0/(A2&B2 = F2 &G2),C2)

J3=LOOKUP(1,0/($A$2:$A$10&$B$2:$B$10=F2&G2),$C$2:$C$10)

兩個公式的結構其實是一樣的，J3是陣列公式，所以除了比對值之外，都是範圍

這邊利用 LOOKUP如果找不到相同的目標值時，會返回小於目標值的最大值(不適用於 0/條件，而是區間查找，可以參考Lookup函數最經典的14種用法)

在第二個參數 0/條件

如果 條件 有符合值，會得到True，0/True=0

沒有符合值，會得到False，0/False會得到#N/A 的錯誤值

如果是陣列公式，就會得到所有的返回值，會是由0或 #N/A組合的陣列

後記

其實如果使用VBA，以程式方式來處理

可能會更直覺、直觀

2023/11/01

Excel / INDEX-SMALL-IF-ROW 萬金油公式

最近在臉書跟Line上加入了幾個excel / excel VBA的社群

除了有很多喜好伸手牌的人會問問題之外

也有很多厲害的人會嘗試去解題

有時候看到所用的 excel函數結構非常複雜，真心覺得厲害

而且看到有巧思的運用函數公式，更會讓我想進一步了解公式是如何組合的

我有空的時候也會嘗試解決一些我看得懂的問題

(必須說，有部分的人連問題都說不清楚。也或者是我程度不夠，無法理解)

發現大多數的問題都是在既有資料中，尋找符合條件的目標

因此，多數的問題可以利用 INDEX-SMALL-IF-ROW 所謂的萬金油公式(用途很多)來處理

而在excel 2019之後的版本，可以用 Filter函數+Match函數來處理，並且自動溢位(也就是不用再按Ctrl+Shift+Enter，設定為陣列公式)

例如：以下資料

條件值：以 牌照號碼 為條件，取得符合條件的其他欄位資料，而這一個條件，會有多筆資料

利用 萬金油公式

E2=INDEX(工作表!A:A,SMALL(IF(工作表!$A$2:$A$435=$C$1,ROW($2:$435),“”),ROW(A1)))

F2=INDEX(工作表!D:D,SMALL(IF(工作表!$A$2:$A$435=$C$1,ROW($2:$435),4^10),ROW(A1)))

G2~I2也都是一樣的公式結構

公式說明：由內而外，由右至左來理解

IF函數 

透過條件 工作表!$A$2:$A$435=$C$1

為True時，取得在ROW($2:$435)範圍內相對位置的值(ROW())

為False時，則為空值(“”跟4^10的結果都是一樣)

最後會得到一個{True/False}的結果陣列

SMALL函數

要找出符合資料在IF陣列結果的位置，以這個資料表來說就會是列數，並配合 ROW(A1)下向拉動，會產生1、2、3….，可以得到第1個符合的值、 第2個符合的值

INDEX函數

利用SMALL函數得到的列數值，回傳對應的欄位值，例如： 某列的在工作表!A欄的資料

利用 Filter函數

E2=FILTER(INDEX(工作表!A2:O435,,MATCH(E$1,工作表!$A$1:$O$1,0)),工作表!$A$2:$A$435=$C$1,””)

F2=FILTER(INDEX(工作表!A2:O435,,MATCH(F$1,工作表!$A$1:$O$1,0)),工作表!$A$2:$A$435=$C$1,””)

G2~I2也都是一樣的公式結構

公式說明：由內而外，由右至左來理解

MATCH函數

取得符合條件(例如： F$1)在目標資料的相對位置，也就是欄位值

INDEX函數

這時候反而是透過MATCH值，取得資料範圍內的符合欄位的所有值

Filter函數

在INDEX函數取得的資料內，透過條件 工作表!$A$2:$A$435=$C$1，篩選出符合條件的值

後記

之後可以再透過一些排除錯誤值的公式，讓結果更乾淨

如果是多個條件，尋找一個值，可以用 VLOOKUP / LOOKUP，這也有很多應用的技巧

2023/10/13

JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改 版3 番外 引用 firebase

JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版 系列 的查詢頁面

所引入的 js 跟 css

都是在後端的程式碼.gs

利用自訂的函數include 使用GAS內建函數 HtmlService.createHtmlOutputFromFile(filename).getContent() 來取得專案中的html檔案內容

然後再利用<?!= ?>標籤來引入與執行後端程式碼 include(filename)，這樣就會將指定的html檔案內容輸出到index.html內

這也是為什麼 js 或 css  的內容必須包在html標籤<script></script> 或<style></style>之內

//讓前端可以載入 css jsfunction include(filename) {  return HtmlService.createHtmlOutputFromFile(filename).getContent();}
<?!=include(style.css) ?>

不過也可以直接使用<?!= ?>標籤來引入GAS程式碼

也就是在index.html頁面直接用 <?!= HtmlService.createHtmlOutputFromFile(“style.css”).getContent(); ?>來引入js 或css檔案

例如：

<?!= HtmlService.createHtmlOutputFromFile("style.css").getContent(); ?>

後來在使用firebase的過程中發現在修改讀取權限之後是可以透過網址連結檔案

在firebase Storage的Files中可以取得檔案的連結

例如：

https://firebasestorage.googleapis.com/v0/b/upload-2a184.appspot.com/o/style.css?alt=media&token=9e31859f-0cb9-428f-9b37-81a5210bc5fb&_gl=1*y1mbre*_ga*MTk4NDQ3MTM3My4xNjkzODg5NDcw*_ga_CW55HF8NVT*MTY5NzEwMjE0Ny4xNi4xLjE2OTcxMDM3MzEuNjAuMC4w

因為已經開放讀取權限，所以token是可以移除

不過 ?alt=media 是不能移除的，一定要帶有這個參數才能讀取檔案內容

所以原本引用的css可以改成

<link rel="stylesheet" href="https://firebasestorage.googleapis.com/v0/b/upload-2a184.appspot.com/o/style.css?alt=media">

2023/09/30

JS / 使用PDF.js建立網頁PDF瀏覽器 番外 firebase

JS / 使用PDF.js建立網頁PDF瀏覽器

有提到師大的網域有同源限制

2023/09/04

JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改 版3

除了嵌到網站之中，或者執行發佈的網頁程式之外 也可以在SpreadsheetApp自訂功能選單 只是這個功能必須綁定自身文件 
Google 應用程式的使用者介面環境範例，可讓指令碼新增選單、對話方塊和側欄等功能。指令碼只能在開啟編輯器的目前執行個體中與 UI 互動，且指令碼必須在編輯器容器繫結範圍內。
程式碼基本沿用 JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版2 增加了以下程式碼 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('查詢表單資料')
      .addItem('啟動表單', 'showDialog')
      .addItem('啟動側欄', 'showSidebar')
      .addToUi();
}

function showDialog() {
  var html = HtmlService.createTemplateFromFile("index").evaluate();
  html.setWidth(880);
  html.setHeight(500);

  SpreadsheetApp.getUi().showModalDialog(html, '查詢表單');  
}

function showSidebar() {
  var html = HtmlService.createTemplateFromFile("index").evaluate();
 
  SpreadsheetApp.getUi().showSidebar(html);
}
 

function onOpen() {}是內建函數

當開啟文件時會執行的程序 這裡是透過getUi()建立使用者介面環境(UI) 增加兩個項目

一個是強制回應對話方塊 (不能點選其他內容) 

function showDialog() {} 對話方塊可以設定寬高與標題

另一個是側欄

function showSidebar() {} 側欄的寬度是固定值(300)
備註： var html = HtmlService.createTemplateFromFile("index").evaluate() var html = HtmlService.createHtmlOutputFromFile("index"); 兩者的差別 Template可以使用Scriptlet，這樣原本在index.Html的程式碼<?!= include("style.css"); ?> 可以正常運作 HtmlOutput無法使用Scriptlet，原本的程式就要大幅修改了，或者直接將要引用的內容置入index.Html裡

2023/09/03

Canva / 將Canva建置的網頁嵌入網站

Canva真是行政工作的好夥伴 
可以設計海報也可以建置簡易的網站

2023/08/31

Word / 使用VBA分割word內的表格 番外2 使用書籤

在 Word / 使用VBA分割word內的表格 番外 
使用word的復原動作來回覆檔案編輯 

2023/08/29

JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改 版2

延續 JS / 以Google sheets作為資料庫，透過Google Apps Script製作查詢頁面 修改版1

 
 
處理問題2. 
 密碼：abc123 
 查詢資料：alice5659@gmail.com

    

 我的思路

1.在工作簿新增一個紀錄密碼的工作表

這其實可以視需求來設定 
 我在彙整所有表單資料的工作簿新增一個名稱為user的工作表
 A欄名稱  password，用來儲存設定的密碼
   

2.新增登入頁面需要的程式碼

前端HTML

主要是多了第2行到第6行，登入的頁面 div login 原來的頁面，包在<div id="searchTable">內 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
<!--登入 密碼 12345678-->
    <div id="login">      
        <label><input id="pd" type="text" placeholder="請輸入密碼"/></label><br />
        <input name="chack" id="chack" onclick="chack();" type="button" value="送出" />
        <input name="clean2" id="cleaner2" onclick="clean2();" type="button" value="清除資料" /><br /><br />
    </div>
    <!--測試資料: mailID:"alice5659@gmail.com", cID:"c0102" -->
    <div id="searchTable">
      <div id="d1">
        <label>電子信箱：<input id="mailID" type="text" /></label><br /><br />
        <label>查詢填寫紀錄</label><br />
        <label><input type="checkbox" name="courses1" value="c0101" />共同課程一 ：低成就學生心理特質、學習動機及教學經營實務案例</label>
        <label><input type="checkbox" name="courses1" value="c0102" />共同課程一 ：數位資源融入學習扶助教學知能</label><br />
        <label><input type="checkbox" name="courses2" value="c02" />共同課程二 ：科技化評量測驗結果應用</label><br />
        <label><input type="checkbox" name="courses3" value="ch01" />分科課程 ：國中國語文</label>
        <label><input type="checkbox" name="courses3" value="ch02" />分科課程 ：國小國語文</label><br />
        <label><input type="checkbox" name="courses3" value="en01" />分科課程 ：國中英語文</label>
        <label><input type="checkbox" name="courses3" value="en02" />分科課程 ：國小英語文</label><br />
        <label><input type="checkbox" name="courses3" value="ma01" />分科課程 ：國中數學&ensp;&ensp;</label>
        <label><input type="checkbox" name="courses3" value="ma02" />分科課程 ：國小數學</label>
      </div>
      <div id="d2">
        <input name="search" id="sender" onclick="sender();" type="button" value="送出" />
        <input name="clean" id="cleaner" onclick="cleanAll();" type="button" value="清除資料" />
      </div>
      <span id="search_status"></span>
      <dialog id="infoModal">
        <p>請輸入查詢資訊!!</p>
        <button id="close">關閉視窗</button>
      </dialog>
    </div>
 

前端JavaScript

增加了這些function
function chack()是取得input資料
避免資料型態的問題，將資料都轉換成字串型態 接著執行 google.script.run.withSuccessHandler(showTable).searchPD(data[0]); 將資料傳到後端的function .searchPD()進行資料比對，然後用function showTable()處理回傳值
備註：在測試function chack()的時候發生很詭異的事情
因為一直抓不到值 後來才發現我根本沒有選到，程式碼少了選擇器 但是我是直接抄前面的function sender()裡面的程式 
var email = mailID.value;
完整的程式碼是這樣 
var email = $('#mailID').val();
或者 
var email = document.getElementById("mailID").value;
完全搞不懂是如何抓到資料的...
function showTable(e)
根據後端function searchPD()回傳值判斷是否顯示div searchTable 以及 隱藏 div login
function clean2()
清除 input 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
function showTable(e) {
  console.log("showTable");
  console.log(e);

  if(e === "good"){
    $('#login').css("display", "none");
    $('#searchTable').css("display", "block");

  }else{
    console.log(e);
    clean2();
    $('#login input[type=button]').css("background-color", "transparent");
  }

}

function chack() {   
  var pd = $('#pd').val();
  console.log(pd);
  var data=[];
  data.push({pd:pd.toString()});
  console.log(data[0]);

  google.script.run.withSuccessHandler(showTable).searchPD(data[0]);//後端執行searchPD  回傳值到showTable
}

function clean2() {
  $("#pd").val("");

}
 

前端CSS

對應DOM物件增加相對應的設定 div searchTable 預設為不顯示 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
 #searchTable {
    display: none;
}

 #login {
    position: absolute;
    top: 50 % ;
    left: 50 % ;
    transform: translate(-50 % , -50 % );
    text - align: center;
}

 #login input {
    /*display: block;*/
    background - color: transparent;
    color:  # e1341e;
    font - size: 36px;
    border: 1px solid # e1341e;
    border - radius: 50px;
    padding: 20px 45px;
    margin - bottom: 30px;
    outline: none;
    transition: all 0.15s ease - in - out;
}

 #login input: focus {
    background - color:  # e1341e;
    color: white;
}

 #login input: focus::placeholder {
    color: white;
}

 #login input[type = button]{
    cursor: pointer;
    color:  # 1ecbe1;
    font - size: 20px;
    /*text-align: center;*/
    border - radius: 30px;
    border: 1px solid # 1ecbe1;
    padding: 10px 10px;
    outline: none;
}

 #login input[type = button]: active {
    background - color:  # ddd;
}
 

後端Gas

處理login傳來的資料 有相對應的資料，回傳 good 沒有符合的資料，回傳 bad 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
function searchPD(e) {
    Logger.log(e);
    var params = e;
    var pd = params.pd; //
    console.log(typeof(pd));
    //
    var ss = SpreadsheetApp.openById("1DyT0547XXrNbt46eAH3qu625lBqiQQ9X5i5k0vTR1Eo");
    var Sheet = ss.getSheetByName("user");

    var LastRow = Sheet.getLastRow();
    var LastColumn = Sheet.getLastColumn();

    var data = []; //用來存資料

    // 取得全部資料
    var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);

    //console.log(listAll[1].toString());

    for (var i = 1; i < listAll.length; i++) {
        if (listAll[i].toString() === pd) {
            data.push(listAll[i].toString());
        }
    }
    console.log(data.length);

    if (data.length > 0) {
        return "good";
    } else {
        return "bad";
    }
}