Google Sheet

Google Sheet Api

Google Sheets Api

若要呼叫此服務，我們建議您使用 Google 提供的用戶端程式庫。如果您的應用程式需要使用您自己的程式庫來呼叫此服務，請在發出 API 請求時使用以下資訊。

Angular

Angular目錄

管理Serve
Angular Rx.js
Angular bootstrap
Angular實戰
Vue

Google Sheet 使用 Google Apps Script 開Api

GetApi

Google Apps Script 是 Google 所開發的腳本平台，

拷貝Google Sheet id

https://docs.google.com/spreadsheets/d/[googlesheetId]/edit?gid=0#gid=0

建立 Google Apps Script

回到 Google 雲端硬碟中建立一個 Google Apps Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function doGet() {
  const sheetId = 'googlesheetId';
  const sheetName = '商品列表'; //文件名稱

  const spreadsheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);

  const keys = sheet.getRange('A1:D1').getValues()[0];
  const values = sheet.getRange('A2:D50').getValues();
    //修改為陣列
  const newData = [];
  values.forEach((data) => {
    newData.push({
      [keys[0]]: data[0],
      [keys[1]]:data[1],
      [keys[2]]:data[2],
      [keys[3]]:data[3],
    })   
  })

  const dataExportFormat = JSON.stringify(newData);
  return ContentService.createTextOutput(dataExportFormat).setMimeType(ContentService.MimeType.JSON);
}

部署公開 複製網址

到 Posman 測試Api

參考資料

https://gist.github.com/ex-preman/e892f72404a0e4779999f8b439cec1b1

Google Driver

Google Driver

Sep1 啟用Api

Google Driver js教學

圖片的使用

取得網址為↓
https://drive.google.com/file/d/1HDh0GvM2Kk-YaqtREE9a3znSrc96kI5c/view?usp=sharing
複製網址中的{id}
https://drive.google.com/file/d/{1RV1W88E5U6ssO1_7ktLEQxtZx-6i2SEo}/view?usp=sharing

https://drive.usercontent.google.com/download?id=1RV1W88E5U6ssO1_7ktLEQxtZx-6i2SEo&export=view&authuser=0

將 id 貼在下列網址末端
https://drive.google.com/uc?export=view&id=1HDh0GvM2Kk-YaqtREE9a3znSrc96kI5c
https://drive.google.com/file/d//view?usp=sharing

Angular 寫法

"'https://drive.google.com/thumbnail?id='+item.image+'&sz=w1366'"
1
<img [src]="'https://drive.google.com/thumbnail?id='+item.image+'&sz=w1366'"/>
Angular

Angular 預先載入資料Resolve

切入頁面要等一下資料才會出來會造成使用者體驗不佳的問題使用 Resolve 預先載入資料

產生resolve的指令

1
ng g c ToDoResolve

放置到src/app/@resolve

  • constructor 依賴注入TodoApiService
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
import { TodoApiService } from './../@services/todo-api.service';
+ import { TodoService } from './../@services/todo.service';
import { Injectable } from '@angular/core';
import {
+  Router, Resolve,
+  RouterStateSnapshot,
+  ActivatedRouteSnapshot
} from '@angular/router';
+import { Observable, of } from 'rxjs';
+import { addListsType } from '../Types/toDo';

+@Injectable({
 + providedIn: 'root'
+})

+export class TodoResolver implements Resolve<addListsType[]> {

+   constructor(
+     private todoApiService: TodoApiService, 
+     private todoService: TodoService
    ) 
    { }

+ resolve(route: ActivatedRouteSnapshot, state: RouterStateSnapshot): Observable<addListsType[]> {
+    return this.todoApiService.取得資料();
+  }
}

該路由設定

1
2
3
4
5
6
const routes: Routes = [
    {
         path: "page", loadComponent: () => import('./page-pra/page-pra.component').then((m) => m.PagePraComponent),
      +  resolve: { todoDataList: TodoResolver }
    }
]

PagePraComponent頁面使用

1
2
3
4
5
6
7
8
constructor(
    private service: TodoService,
+    private route: ActivatedRoute
  ) { }

  ngOnInit() {
 +    this.service.todoDataList = this.route.snapshot.data['todoDataList']
  }

Resolve 預先載入資料參考資料github
Resolve 預先載入資料參考資料

Angular

Angular Router CanActivate路由導航守衛

app資料夾新增 @guard 資料夾內新增 auth.guard.ts檔案中新增以下設定:
  • 引入 Injectableimport { Injectable } from '@angular/core';
  • 引入 ActivatedRouteSnapshot, CanActivate, Router, RouterStateSnapshot, UrlTreeimport { ActivatedRouteSnapshot, CanActivate, Router, RouterStateSnapshot, UrlTree } from '@angular/router';
  • 引入 Observable import { Observable } from 'rxjs';
  • class AuthGuard implements CanActivate 內
    constructor(private router: Router) { }
    canActivate( localStorage.getItem('token') 有登入並且沒有過期的jwt，沒有時，導到登入頁面 )
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
import { Injectable } from '@angular/core';
import { ActivatedRouteSnapshot, CanActivate, Router, RouterStateSnapshot, UrlTree } from '@angular/router';
import { Observable } from 'rxjs';

//裝飾器
@Injectable({
  providedIn: 'root'
})

export class AuthGuard implements CanActivate {

  constructor(private router: Router) { }

  canActivate(
    route: ActivatedRouteSnapshot,
    state: RouterStateSnapshot): Observable<boolean | UrlTree> | Promise<boolean | UrlTree> | boolean | UrlTree {

       const jwt = localStorage.getItem('token');
      if (jwt) {
        const payload = JSON.parse(window.atob(jwt.split('.')[1]));
        const exp = new Date(Number(payload.exp) * 1000);
        if (new Date() > exp) {
          alert('JWT已過期，請重新登入');
          return this.router.createUrlTree(['/login']);
        }
      } else {
        alert('尚未登入');
        return this.router.createUrlTree(['/login']);
      }
      return true;
  }

  canActivateChild(childRoute: ActivatedRouteSnapshot, state: RouterStateSnapshot): boolean | UrlTree | Observable<boolean | UrlTree> | Promise<boolean | UrlTree> {
    return this.canActivate(childRoute, state);
  }

}
Angular

Angular ng-bootstrap 分頁搜尋排序

Table Sort Serarh Pagination

Ng-bootstrap安裝參考

屬性
檔案名稱:country.ts 
1
2
3
4
5
6
7
8
//country.ts
export interface Country {
	id: number;
	name: string;
	flag: string;
	area: number;
	population: number;
}
陣列資料數據
檔案名稱:countries.ts 
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
//countries.ts
import { Country } from './country';

export const COUNTRIES: Country[] = [
	{
		id: 1,
		name: 'Russia',
		flag: 'f/f3/Flag_of_Russia.svg',
		area: 17075200,
		population: 146989754,
	},
	{
		id: 2,
		name: 'France',
		flag: 'c/c3/Flag_of_France.svg',
		area: 640679,
		population: 64979548,
	},
	{
		id: 3,
		name: 'Germany',
		flag: 'b/ba/Flag_of_Germany.svg',
		area: 357114,
		population: 82114224,
	},
	{
		id: 4,
		name: 'Portugal',
		flag: '5/5c/Flag_of_Portugal.svg',
		area: 92090,
		population: 10329506,
	},
	{
		id: 5,
		name: 'Canada',
		flag: 'c/cf/Flag_of_Canada.svg',
		area: 9976140,
		population: 36624199,
	},
	{
		id: 6,
		name: 'Vietnam',
		flag: '2/21/Flag_of_Vietnam.svg',
		area: 331212,
		population: 95540800,
	},
	{
		id: 7,
		name: 'Brazil',
		flag: '0/05/Flag_of_Brazil.svg',
		area: 8515767,
		population: 209288278,
	},
	{
		id: 8,
		name: 'Mexico',
		flag: 'f/fc/Flag_of_Mexico.svg',
		area: 1964375,
		population: 129163276,
	},
	{
		id: 9,
		name: 'United States',
		flag: 'a/a4/Flag_of_the_United_States.svg',
		area: 9629091,
		population: 324459463,
	},
	{
		id: 10,
		name: 'India',
		flag: '4/41/Flag_of_India.svg',
		area: 3287263,
		population: 1324171354,
	},
	{
		id: 11,
		name: 'Indonesia',
		flag: '9/9f/Flag_of_Indonesia.svg',
		area: 1910931,
		population: 263991379,
	},
	{
		id: 12,
		name: 'Tuvalu',
		flag: '3/38/Flag_of_Tuvalu.svg',
		area: 26,
		population: 11097,
	},
	{
		id: 13,
		name: 'China',
		flag: 'f/fa/Flag_of_the_People%27s_Republic_of_China.svg',
		area: 9596960,
		population: 1409517397,
	},
];

country.service.ts 
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
/* eslint-disable @typescript-eslint/adjacent-overload-signatures */
import { Injectable, PipeTransform } from '@angular/core';
//https://ithelp.ithome.com.tw/m/articles/10223920
import { BehaviorSubject, Observable, of, Subject } from 'rxjs';

import { Country } from './country'; //數據屬性
import { COUNTRIES } from './countries';//陣列資料數據
import { DecimalPipe } from '@angular/common';
import { debounceTime, delay, switchMap, tap } from 'rxjs/operators';
import { SortColumn, SortDirection } from './sortable.directive';

//搜尋結果屬性
interface SearchResult {
	countries: Country[];
	total: number;
}

interface State {
	page: number;
	pageSize: number;
	searchTerm: string;
	sortColumn: SortColumn;
	sortDirection: SortDirection;
}
// 比較
const compare = (v1: string | number, v2: string | number) => (v1 < v2 ? -1 : v1 > v2 ? 1 : 0);
// 排序:城市,行,描述
function sort(countries: Country[], column: SortColumn, direction: string): Country[] {
	if (direction === '' || column === '') {
		return countries;
	} else {
		return [...countries].sort((a, b) => {
			const res = compare(a[column], b[column]);
			return direction === 'asc' ? res : -res;
		});
	}
}
匹配
function matches(country: Country, term: string, pipe: PipeTransform) {
	return (
		country.name.toLowerCase().includes(term.toLowerCase()) ||
		pipe.transform(country.area).includes(term) ||
		pipe.transform(country.population).includes(term)
	);
}

@Injectable({ providedIn: 'root' })
export class CountryService {
	private _loading$ = new BehaviorSubject<boolean>(true);
	private _search$ = new Subject<void>();
	private _countries$ = new BehaviorSubject<Country[]>([]);
	private _total$ = new BehaviorSubject<number>(0);

	private _state: State = {
		page: 1,
		pageSize: 4,
		searchTerm: '',
		sortColumn: '',
		sortDirection: '',
	};
    // 建構函數
    //DecimalPipe 小數點. 把數字轉換成字串， 根據本地化規則進行格式化，這些規則會決定分組大小和分組分隔符、小數點字元以及其它與本地化環境有關的配置項。
	constructor(private pipe: DecimalPipe) {
		this._search$
			.pipe(
				tap(() => this._loading$.next(true)),
				debounceTime(200),
				switchMap(() => this._search()),
				delay(200),
				tap(() => this._loading$.next(false)),
			)
			.subscribe((result) => {
				this._countries$.next(result.countries);
				this._total$.next(result.total);
			});

		this._search$.next();
	}

	get countries$() {
		return this._countries$.asObservable();
	}
	get total$() {
		return this._total$.asObservable();
	}
	get loading$() {
		return this._loading$.asObservable();
	}
	get page() {
		return this._state.page;
	}
	get pageSize() {
		return this._state.pageSize;
	}
	get searchTerm() {
		return this._state.searchTerm;
	}

	set page(page: number) {
		this._set({ page });
	}
	set pageSize(pageSize: number) {
		this._set({ pageSize });
	}
	set searchTerm(searchTerm: string) {
		this._set({ searchTerm });
	}
	set sortColumn(sortColumn: SortColumn) {
		this._set({ sortColumn });
	}
	set sortDirection(sortDirection: SortDirection) {
		this._set({ sortDirection });
	}

	private _set(patch: Partial<State>) {
		Object.assign(this._state, patch);
		this._search$.next();
	}

	private _search(): Observable<SearchResult> {
		const { sortColumn, sortDirection, pageSize, page, searchTerm } = this._state;

		// 1. sort
		let countries = sort(COUNTRIES, sortColumn, sortDirection);

		// 2. filter
		countries = countries.filter((country) => matches(country, searchTerm, this.pipe));
		const total = countries.length;

		// 3. paginate
		countries = countries.slice((page - 1) * pageSize, (page - 1) * pageSize + pageSize);
		return of({ countries, total });
	}
}

排序
sortable.directive.ts 
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
// sortable.directive.ts
import { Directive, EventEmitter, Input, Output } from '@angular/core';
import { Country } from './country';

export type SortColumn = keyof Country | '';
export type SortDirection = 'asc' | 'desc' | '';
const rotate: { [key: string]: SortDirection } = { asc: 'desc', desc: '', '': 'asc' };

export interface SortEvent {
	column: SortColumn;
	direction: SortDirection;
}

@Directive({
	selector: 'th[sortable]',
	standalone: true,
	host: {
		'[class.asc]': 'direction === "asc"',
		'[class.desc]': 'direction === "desc"',
		'(click)': 'rotate()',
	},
})
// 可排序標頭
export class NgbdSortableHeader {
    //接收父元件
	@Input() sortable: SortColumn = '';
	@Input() direction: SortDirection = '';

	@Output() sort = new EventEmitter<SortEvent>();

	rotate() {
		this.direction = rotate[this.direction];
		this.sort.emit({ column: this.sortable, direction: this.direction });
	}
}

users.component.ts
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
// users.component.ts
import { AsyncPipe, DecimalPipe, NgFor, NgIf } from '@angular/common';
import { Component, QueryList, ViewChildren } from '@angular/core';
import { Observable } from 'rxjs';
import { Country } from './country';//屬性
import { CountryService } from './country.service';
import { NgbdSortableHeader, SortEvent } from './sortable.directive';
import { FormsModule } from '@angular/forms';
import { NgbHighlight, NgbPaginationModule ,NgbDropdownConfig, NgbDropdownModule} from '@ng-bootstrap/ng-bootstrap'; 

@Component({
  selector: 'app-users',
  standalone: true,
  imports: [ NgIf, NgFor,DecimalPipe, FormsModule, AsyncPipe, NgbHighlight, NgbdSortableHeader, NgbPaginationModule,NgbDropdownModule],
  templateUrl: './users.component.html',
  styleUrl: './users.component.scss',
  providers: [CountryService, DecimalPipe,NgbDropdownConfig],
})

export class UsersComponent {
  countries$: Observable<Country[]>;
  total$: Observable<number>;

  @ViewChildren(NgbdSortableHeader) headers: QueryList<NgbdSortableHeader> | undefined;

  constructor(public service: CountryService) {
		this.countries$ = service.countries$;
		this.total$ = service.total$;
  }

  onSort({ column, direction }: SortEvent) {
		// this.headers.forEach((header) => {
		// 	if (header.sortable !== column) {
		// 		header.direction = '';
		// 	}
		// });

		this.service.sortColumn = column;
		this.service.sortDirection = direction;
  }
   pagePerOptions = [
   { value: 5, label: '5筆' },
    { value: 10, label: '10筆' },
    { value: 15, label: '15筆' },
    { value: 20, label: '20筆' },
    { value: 30, label: '30筆' },
    { value: 40, label: '40筆' },
    { value: 50, label: '50筆' },
  ]
  changeItemsPerPage(item: any) {
    this.service.pageSize = item.value;
  }
}

Table,Sort,Search,Pagination Html
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
<p>這是一個更完整的範例，其中包含模擬伺服器呼叫的服務：:</p>

<ul>
	<li>一個可觀察的非同步服務，用於取得國家清單</li>
<li>排序、過濾和分頁</li>
<li>模擬延遲和載入指示器</li>
<li>消除搜尋請求的彈跳</li>
</ul>
<div class="container">
  <form>
    <div class="mb-3 row">
      <div class="col">
        <input
          id="table-complete-search"
          type="text"
          class="form-control"
          name="searchTerm"
          [(ngModel)]="service.searchTerm"
        />
      </div>

        <span *ngIf="service.loading$ | async" class="col col-form-label">Loading...</span>

    </div>
    <table class="table table-striped default_table">
      <thead>
        <tr>
          <th scope="col">#</th>
          <th scope="col" sortable="name" (sort)="onSort($event)">Country</th>
          <th scope="col" sortable="area" (sort)="onSort($event)">Area</th>
          <th scope="col" sortable="population" (sort)="onSort($event)">Population</th>
        </tr>
      </thead>
      <tbody>
        <!-- //@for (country of countries$ | async; track country.id) { -->

        <tr *ngFor="let country of countries$ | async">
            <th scope="row">{{ country.id }}</th>
            <td>
              <img
                [src]="'https://upload.wikimedia.org/wikipedia/commons/' + country.flag"
                [alt]="'The flag of ' + country.name"
                class="me-2"
                style="width: 20px"
              />
              <ngb-highlight [result]="country.name" [term]="service.searchTerm" />
            </td>
            <td><ngb-highlight [result]="country.area | number" [term]="service.searchTerm" /></td>
            <td><ngb-highlight [result]="country.population | number" [term]="service.searchTerm" /></td>
          </tr>
        <!-- } @empty {
          <tr>
            <td colspan="4" style="text-align: center">No countries found</td>
          </tr>
        } -->
      </tbody>
    </table>

    <div class="d-flex justify-content-center ng_pagination_area mt-3">
      <!---分頁-->
      <ngb-pagination
      [collectionSize]="(total$ | async)!"
      [(page)]="service.page"
      [maxSize]="5"
      [rotate]="true"
      [ellipses]="false"
      [boundaryLinks]="true"
      [pageSize]="service.pageSize"
      >
        <ng-template ngbPaginationFirst>
          <i class="fa-solid fa-angles-left"></i>
          </ng-template>
        <ng-template ngbPaginationPrevious>
          <i class="fa-solid fa-chevron-left"></i>
        </ng-template>
        <ng-template ngbPaginationNext>
          <i class="fa-solid fa-chevron-right"></i>
        </ng-template>
        <ng-template ngbPaginationLast>
          <i class="fa-solid fa-angles-right"></i>
        </ng-template>
      </ngb-pagination>

  <div ngbDropdown>
    <button type="button" class="btn" id="dropdownConfig" ngbDropdownToggle>筆數</button>
    <div ngbDropdownMenu aria-labelledby="dropdownConfig">
      <button ngbDropdownItem
      *ngFor="let item of pagePerOptions"
      (click)="changeItemsPerPage(item)">{{item.label}}</button>
    </div>
    </div>
    </div>
  </form>
</div>
樣式
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
//table
.default_table{
  margin: auto;
  .row{
     width: 100%;
    max-width: 100%;
  }
}
.form-control{
  width: 100%;
  max-width: 100%;
  border:2px solid rgb(44, 120, 235);
  :focus {
    color: var(--bs-body-color);
    background-color: var(--bs-body-bg);
    border-color: rgb(134, 182.5, 254);
    outline: 0;
    box-shadow: 0 0 0 0.25rem rgba(13, 110, 253, 0.25);
}
}
// 分頁Pagination
.page-link:focus {
    background-color: transparent;
    box-shadow: inset 1px -1px 0 0.25rem  transparent;
}
a:-webkit-any-link:focus-visible {
    outline-offset: 0px;
}
a:hover {
    --bs-link-color-rgb:  transparent;
}
:focus-visible {
    outline: white auto 0px;
}
.ng_pagination_area {
  .pagination{
    li {
      &:first-child a {
        span {
          display: none;
        }
      }
    }
    .active > {
      .page-link{
        color:green;
        background-color: transparent;
        border-radius: 50%;
        border:  2px solid green;
        &:hover,&:focus{
          background-color: transparent;
        }
      }
    }
    .page-item{
      .page-link{
        color:green;
        border-color: transparent;
        background-color: transparent;
        &:hover,&:focus{
          background-color: transparent;
          border: 0px solid transparent;
        }
      }
      &.disabled{
        .page-link{
          color:#ddd;
        }
      }
      &:not( :first-child,:last-child,:nth-child(2),:nth-last-child(2) ){
        .page-link{
          &:hover,&:focus{
            background-color: transparent;
            border-radius: 50%;
            border:  2px solid green;
          }
        }
      }
      &:not(:first-child){ color:red; }
      }
  }
}
/*下拉元件*/
.btn{
    border:2px solid #ffd207;
    &:hover,&:focus{
       background-color: #ffd207;
      color:#333;
    }
}
.dropup{
   margin-left: 10px;
  .dropdown-toggle{
    border-color:#ffd207;
    color:#333;
  }
  .btn{
    border:1px solid #ffd207;
    --bs-btn-padding-x: 0.75rem;
    --bs-btn-padding-y: 0.175rem;
    &:hover,&:focus{
      background-color: #ffd207;
      color:#333;
    }
  }
  .dropdown-menu{
    min-width: 50px;
    &.show {
      min-width: 50px
    }
    .dropdown-item{
      &.active,
      &:active{
      background-color: #ffd207;
        color:#333;
      }
    }
  }
}
.dropdown-menu{
    min-width: 50px;
    &.show {
      min-width: 50px
    }
}
.btn-check:checked + .btn,
:not(.btn-check) +
.btn:active,
.btn:first-child:active,
.btn.active,
.btn.show {
    color:#333;
    background-color:#ffd207;
    border-color: #ffd207;
}

Github 範例