如何將 PayPal 交易導入 Google 表格

 /* @OnlyCurrentDoc */ /* Author: digitalinspiration.com */ const TRANSACTION_TYPE = 'T0001' ; // Enter your own PayPal Client ID and Client Secret key const PAYPAL_CLIENT_ID = '<YOUR_PAYPAL_CLIENT_ID>' ; const PAYPAL_CLIENT_SECRET = '<YOUR_PAYPAL_CLIENT_SECRET>' ; // Enter start and end dates in the format YYYY-MM-DD const START_DATE = '2022-03-01' ; const END_DATE = '2022-03-15' ; // Generate the PayPal access token const getPayPalAccessToken_ = ( ) => { const credentials = ` ${ PAYPAL_CLIENT_ID } : ${ PAYPAL_CLIENT_SECRET } ` ; const headers = { Authorization : ` Basic ${ Utilities . base64Encode ( credentials ) } ` , Accept : 'application/json' , 'Content-Type' : 'application/json' , 'Accept-Language' : 'en_US' , } ; const options = { method : 'POST' , headers , contentType : 'application/x-www-form-urlencoded' , payload : { grant_type : 'client_credentials' } , } ; const request = UrlFetchApp . fetch ( 'https://api.paypal.com/v1/oauth2/token' , options ) ; const { access_token } = JSON . parse ( request ) ; return access_token ; } ; // Append the query parameters to the PayPal API URL const buildAPIUrl_ = ( queryParams ) => { const baseUrl = [ ` https://api-m.paypal.com/v1/reporting/transactions ` ] ; Object . entries ( queryParams ) . forEach ( ( [ key , value ] , index ) => { const prefix = index === 0 ? '?' : '&' ; baseUrl . push ( ` ${ prefix } ${ key } = ${ value } ` ) ; } ) ; return baseUrl . join ( '' ) ; } ; // Fetch the list of PayPal transaction const fetchTransactionBatchFromPayPal = ( queryParams ) => { const options = { headers : { Authorization : ` Bearer ${ getPayPalAccessToken_ ( ) } ` , 'Content-Type' : 'application/json' , } , } ; const request = UrlFetchApp . fetch ( buildAPIUrl_ ( queryParams ) , options ) ; const { transaction_details , total_pages } = JSON . parse ( request ) ; return { transaction_details , total_pages } ; } ; // Extract the transaction details including the transaction ID, // donation amount, transaction date and buyer's email and country code const parsePayPalTransaction_ = ( { transaction_info , payer_info } ) => [ transaction_info . transaction_id , new Date ( transaction_info . transaction_initiation_date ) , transaction_info . transaction_amount ?. value , transaction_info . transaction_note || transaction_info . transaction_subject || '' , payer_info ?. payer_name ?. alternate_full_name , payer_info ?. email_address , payer_info ?. country_code , ] ; const fetchPayPalTransactions_ = ( ) => { const startDate = new Date ( START_DATE ) ; const endDate = new Date ( END_DATE ) ; startDate . setHours ( 0 , 0 , 0 , 0 ) ; endDate . setHours ( 23 , 59 , 59 , 999 ) ; const transactions = [ ] ; const params = { start_date : startDate . toISOString ( ) , end_date : endDate . toISOString ( ) , page_size : 100 , transaction_type : TRANSACTION_TYPE , fields : 'transaction_info,payer_info' , } ; for ( let page = 1 , hasMore = true ; hasMore ; page += 1 ) { const response = fetchTransactionBatchFromPayPal ( { ... params , page } ) ; const { transaction_details = [ ] , total_pages } = response ; transaction_details . map ( parsePayPalTransaction_ ) . forEach ( ( e ) => transactions . push ( e ) ) ; hasMore = total_pages && total_pages > page ; } return transactions ; } ; // Import the transactions from PayPal and write them to the active Google Sheet const importTransactionsToGoogleSheet = ( ) => { const transactions = fetchPayPalTransactions_ ( ) ; const { length } = transactions ; if ( length > 0 ) { const sheet = SpreadsheetApp . getActiveSheet ( ) ; sheet . getRange ( 1 , 1 , length , transactions [ 0 ] . length ) . setValues ( transactions ) ; const status = ` Imported ${ length } PayPal transactions into Google Sheets ` ; SpreadsheetApp . getActiveSpreadsheet ( ) . toast ( status ) ; } } ;