---
title: 'Google Sheet Api (2)'
disqus: hackmd
---
###### tags: `Sheet API`
Google Sheet Api (2)
===

[TOC]

## 筆記目的

在前篇筆記中已經完成串接Google Ad APi，這個筆記則是更深入的去理解一下google
sheet api的架構，讓人可以完成read write delete update 等動作

具體請參考:

* [A1 notation](https://developers.google.com/sheets/api/guides/concepts#expandable-1)
* [基本寫入使用postman](https://justcode.ikeepstudying.com/2017/05/java-google-sheet-api-v4-%E5%85%A5%E9%97%A8%E4%BA%8C-%EF%BC%9A%E5%9F%BA%E6%9C%AC%E5%86%99%E5%85%A5/)

## 程式碼部分

第一篇文章並沒有將共同會重複使用的部分獨立成為class，這篇文章將會示範如何將共同使用的部分獨立出來，使用的方式很簡單，在main function裡面使用`Sheets googlesheetservice=GoogleSheetsAuthorizedServiceAgent.getAuthorizedSheetsService();`之後拿到service之後便可以對

### 專案架構


### GoogleSheetsAuthorizedServiceAgent

package com.google.sheet.agent;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.List;

import org.slf4j.Logger;
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;

public class GoogleSheetsAuthorizedServiceAgent {
	
	
	
	private static final String APPLICATION_NAME = "ComprehensiveServiceCenter-GoogleSheetsAPI";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final String TOKENS_DIRECTORY_PATH = "tokens";
    
    /*
     * https://developers.google.com/sheets/api/quickstart/java
     */
    
    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved tokens/ folder.
     * 這邊可以控制呼叫Sheet API 的使用權限
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.DRIVE);
    
    
    /*
     * 此份檔案得從Google Api console 後台 開啟Google Sheets服務後就可以取得該份檔案
     * 將此份檔案放在 /src/main/resources 即可
     */
    private static final String CREDENTIALS_FILE_PATH = "/credentialsforsheet.json";

    /**
     * Creates an authorized Credential object.
     * @param HTTP_TRANSPORT The network HTTP Transport.
     * @return An authorized Credential object.
     * @throws IOException If the credentials.json file cannot be found.
     */
    private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
    	// Load client secrets.
    	//Google 官方範例提供方式
    	InputStream in = GoogleSheetsAuthorizedServiceAgent.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        
        
        if (in == null) {
        
        	throw new FileNotFoundException("Resource not found: " + CREDENTIALS_FILE_PATH);
        }
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
        
        /*
         * 呼叫使用Drive Api 會去認證授權給定一組Token 並會儲存下來一份檔案(StoredCredential)
         * 但是要注意,因為這個StoredCredential 當第一次呼叫時 都得手動連結一個 授權網址 如下範例
         * Please open the following address in your browser:
  				https://accounts.google.com/o/oauth2/auth?access_type=offline&client_id=908400561361-9lsm2ilr9f066tcndks1v0nkofp4tibb.apps.googleusercontent.com&redirect_uri=http://localhost:55423/Callback&response_type=code&scope=https://www.googleapis.com/auth/drive
         * 因此當要佈署到linux上時,得一併將測試環境已經授權過的StoredCredential 一併copy過去對應的路徑上
         * 否則得手動再授權一次, 且該授權動作還會callback  因為callback URL都是=localhost 因此無法成功 ==> 結論還是乖乖複製吧
         */
        File tokenDirectory = new java.io.File(TOKENS_DIRECTORY_PATH);
      
        
        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(tokenDirectory))
                .setAccessType("offline")
                .build();
        
        //Google官方sample,屬於指定port的方式 當取得google確認授權後的callback url:port
        // Build flow and trigger user authorization request.
//        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
//                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
//                .setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
//                .setAccessType("offline")
//                .build();
//        LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
//        return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
        
        //也可以不指定port,隨機配給一組port
        return new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
    }
    
    public static Sheets getAuthorizedSheetsService() throws GeneralSecurityException, IOException {
    	// Build a new authorized API client service.
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
        
        return service;
    }//End function
	
	

}//End class

### main for read

package com.google.sheet;

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.List;

import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.sheet.agent.GoogleSheetsAuthorizedServiceAgent;

public class SheetTest {
	
	public static void main(String[] args) throws GeneralSecurityException, IOException {
		Sheets googlesheetservice=GoogleSheetsAuthorizedServiceAgent.getAuthorizedSheetsService();
	     final String spreadsheetId = "1z078C9BH5rjQ01SYhlWLNXrscflCaua3lVx3dPi3l1w";
	     final String range = "Class Data!A2:B";
	     ValueRange response = googlesheetservice.spreadsheets().values()
	                .get(spreadsheetId, range)
	                .execute();
	        List<List<Object>> values = response.getValues();
	        if (values == null || values.isEmpty()) {
	            System.out.println("No data found.");
	        } else {
	            System.out.println("Name, score");
	            
	            for (List row : values) {
	                // Print columns A and E, which correspond to indices 0 and 4.
	                System.out.printf("%s, %s\n", row.get(0), row.get(1));
	            
	            } //end for loop
	       
	        } //end for else
	
	}//end for main
	
	
}

### main for write and update

package com.google.sheet;

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.List;

import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.sheet.agent.GoogleSheetsAuthorizedServiceAgent;

public class SheetTest {
	
	public static void main(String[] args) throws GeneralSecurityException, IOException {
		Sheets googlesheetservice=GoogleSheetsAuthorizedServiceAgent.getAuthorizedSheetsService();
	     final String spreadsheetId = "1z078C9BH5rjQ01SYhlWLNXrscflCaua3lVx3dPi3l1w";
	     final String range = "Class Data!A2:B4";
	
	        List<List<Object>> values1 = Arrays.asList(
	        		Arrays.asList("2020-06-25", 300.0),
					Arrays.asList("20200625", 8888)

	        );
	        ValueRange body = new ValueRange()
	                .setValues(values1);
	        UpdateValuesResponse result =
	        		googlesheetservice.spreadsheets().values().update(spreadsheetId, range, body)
	                        .setValueInputOption("RAW")
	                        .execute();
	        
	}//end for main
	
	
}//end for SheetTest class 

## write spreadsheet function

private String writeDataInSpreadsheetByUpdateMode(Sheets sheetsService, String spreadsheetId, String range, List<List<Object>> updateValues) throws InterruptedException {
		//將預備寫入陣列的值寫入到Spreadsheet
        /**
         * 要將值寫入到Spreadsheet必須符合特定的格式,詳細請參考底下Google API網頁
         * https://developers.google.com/sheets/api/guides/values
         * 由於底下是寫入單欄但不同列的值所以格式必須符合
         * 
         * List<List<Object>> values = Arrays.asList(
         * 			Arrays.asList("A"),//第1列
         * 			Arrays.asList("B"),//第2列
         *  		Arrays.asList("C"),//第3列
         *  		...........
         * );
         */
		
		String replyStatus = "";
		boolean retry = true;
		int retryCount = 0;
		while(retry) {
			try {
				if(sheetsService == null) {sheetsService = GoogleSheetsAuthorizedServiceAgent.getAuthorizedSheetsService();}
	        	
	        	ValueRange finishBody = new ValueRange()
	        	        .setValues(updateValues);
	        	UpdateValuesResponse updateFinishedResult =
	        			sheetsService.spreadsheets().values().update(spreadsheetId, range, finishBody)
	        	        .setValueInputOption("RAW")
	        	        .execute();
	        	
	        	logger.info("writeDataInSpreadsheetByUpdateMode<" + spreadsheetId + "," + range + "> Finish....");
				retry = false;
				replyStatus = "";
			} catch (GoogleJsonResponseException jgre) {
				replyStatus = EXECUTION_STATUS_SpreadsheetByUpdateMode_GoogleJsonResponseException;
				logger.error("writeDataInSpreadsheetByUpdateMode()...GoogleJsonResponseException Happened...");
				logger.error(jgre.toString());
				if(jgre.toString().indexOf("RESOURCE_EXHAUSTED") !=-1) {
					logger.warn("Sheets service Quota exceeded....getTargetCampaignUpdateRangeFromSpreadsheetOfSettingTable()");
					retryCount++;
					retry = (retryCount <= MaxRetryCount) ? true : false;
					if(retry) {
						retrySleepDuration = MathematicsBasicOperationAgent.getIntegerRandomNumberInRange(retrySleepMinimumSeconds, retrySleepMaximumSeconds);
						logger.error("[Retry Count: " + retryCount + "] will sleep " + retrySleepDuration + " secs then try again");
						Thread.sleep(retrySleepDuration * 1000L);
					}
				}
			} catch(Exception e) {
				replyStatus = EXECUTION_STATUS_SpreadsheetByUpdateMode_Exception;
				logger.error("writeDataInSpreadsheetByUpdateMode()...Exception Happened...");
				logger.error(e.toString());
			}//End catch
		}//End while
		
		return replyStatus;
	}//End

## get usefulrange code example

private List<List<Object>> getListObjectDataOfAppendMode(
			SpreadsheetFramePropertiesDataBean currentCampaign_SpreadsheetFramePropertiesDataBean, 
			List<CommonPerformanceReportBean> currentCampaignPer,
			HashMap<String, CommonPerformanceReportBean> currentCampaignPerViewByDateMap, 
			HashMap<String, GAds_DailyCampaignSettingEntity> currentCampaign_DailySettingMap, 
			String aiKpiType) {
		List<List<Object>> result = new ArrayList<List<Object>>();
//		List<List<Object>> appendValues = 
//    			Arrays.asList(
//    					Arrays.asList("2020-06-25", 300.0, "Cell_C2", "", "Cell_E2"),
//    					Arrays.asList("20200625", 8888, "Cell_C3", "", ""),
//    					Arrays.asList(),
//    					Arrays.asList("2020/06/25", 666.6, "Cell_C5", "", "Cell_E5", "Cell_F5")
//    					);
		List<GoogleSpreadsheet_Ai_CPV_DataBean> cpvDataResultList = null;
		List<GoogleSpreadsheet_Ai_CPC_DataBean> cpcDataResultList = null;
		List<GoogleSpreadsheet_Ai_CPA_DataBean> cpaDataResultList = null;
		
//		CommonPerformanceReportBean commonPerformanceReportBean = null;
		GAds_DailyCampaignSettingEntity campaignDailySettingEntity = null;
		if(aiKpiType.equalsIgnoreCase("CPV")) {
			cpvDataResultList = (List<GoogleSpreadsheet_Ai_CPV_DataBean>) currentCampaign_SpreadsheetFramePropertiesDataBean.getResult();
			HashMap<String, GoogleSpreadsheet_Ai_CPV_DataBean> cpvDataResultMap = convertGoogleSpreadsheet_Ai_CPV_DataBeanListIntoDateViewMap(cpvDataResultList);
			
			for(CommonPerformanceReportBean eachPer : currentCampaignPer) {
				if(!cpvDataResultMap.containsKey(eachPer.getDataDate())) {
					campaignDailySettingEntity = (currentCampaign_DailySettingMap.containsKey(eachPer.getDataDate().replaceAll("-", ""))) ? currentCampaign_DailySettingMap.get(eachPer.getDataDate().replaceAll("-", "")) : null;
					Double dailyBudget = (campaignDailySettingEntity != null) ? campaignDailySettingEntity.getDailyBudget(): 0.0;
					Double spendRatio = getSpendRatioValue(dailyBudget, eachPer.getCosts());
					result.add(
							Arrays.asList(
									eachPer.getDataDate(), 
									dailyBudget, 
									eachPer.getCosts(), 
									spendRatio, 
									eachPer.getImpressions(), 
									eachPer.getVideoViews(), 
									eachPer.getAverageCpv(), 
									MathematicsBasicOperationAgent.getDoubleByDivision(eachPer.getVideoViewRate(), 100.0, 4)
									)
							);
				}
			}
		} else if(aiKpiType.equalsIgnoreCase("CPC")) {
			cpcDataResultList = (List<GoogleSpreadsheet_Ai_CPC_DataBean>) currentCampaign_SpreadsheetFramePropertiesDataBean.getResult();
			HashMap<String, GoogleSpreadsheet_Ai_CPC_DataBean> cpcDataResultMap = convertGoogleSpreadsheet_Ai_CPC_DataBeanListIntoDateViewMap(cpcDataResultList);
			
			for(CommonPerformanceReportBean eachPer : currentCampaignPer) {
				if(!cpcDataResultMap.containsKey(eachPer.getDataDate())) {
					campaignDailySettingEntity = (currentCampaign_DailySettingMap.containsKey(eachPer.getDataDate().replaceAll("-", ""))) ? currentCampaign_DailySettingMap.get(eachPer.getDataDate().replaceAll("-", "")) : null;
					Double dailyBudget = (campaignDailySettingEntity != null) ? campaignDailySettingEntity.getDailyBudget(): 0.0;
					Double spendRatio = getSpendRatioValue(dailyBudget, eachPer.getCosts());
					Double impressionShare = getImpressionShareRelatedMetricsValue("impressionshare", eachPer);
					Double absoluteTopImpressionPercentage = getImpressionShareRelatedMetricsValue("absolutetopimpressionpercentage", eachPer);
					Double budgetLostImpressionShare = getImpressionShareRelatedMetricsValue("budgetlostimpressionshare", eachPer);
					Double rankLostImpressionShare = getImpressionShareRelatedMetricsValue("ranklostimpressionshare", eachPer);
					
					result.add(
							Arrays.asList(
									eachPer.getDataDate(), 
									dailyBudget, 
									eachPer.getCosts(), 
									spendRatio, 
									eachPer.getImpressions(), 
									eachPer.getClicks(), 
									eachPer.getAverageCpc(), 
									MathematicsBasicOperationAgent.getDoubleByDivision(eachPer.getCtr(), 100.0, 4),
									impressionShare,
									absoluteTopImpressionPercentage,
									budgetLostImpressionShare,
									rankLostImpressionShare
									)
							);
				}
			}
		} else if(aiKpiType.equalsIgnoreCase("CPA")) {
			cpaDataResultList = (List<GoogleSpreadsheet_Ai_CPA_DataBean>) currentCampaign_SpreadsheetFramePropertiesDataBean.getResult();
			HashMap<String, GoogleSpreadsheet_Ai_CPA_DataBean> cpaDataResultMap = convertGoogleSpreadsheet_Ai_CPA_DataBeanListIntoDateViewMap(cpaDataResultList);
			
			for(CommonPerformanceReportBean eachPer : currentCampaignPer) {
				if(!cpaDataResultMap.containsKey(eachPer.getDataDate())) {
					campaignDailySettingEntity = (currentCampaign_DailySettingMap.containsKey(eachPer.getDataDate().replaceAll("-", ""))) ? currentCampaign_DailySettingMap.get(eachPer.getDataDate().replaceAll("-", "")) : null;
					Double dailyBudget = (campaignDailySettingEntity != null) ? campaignDailySettingEntity.getDailyBudget(): 0.0;
					Double spendRatio = getSpendRatioValue(dailyBudget, eachPer.getCosts());
					result.add(
							Arrays.asList(
									eachPer.getDataDate(), 
									dailyBudget, 
									eachPer.getCosts(), 
									spendRatio, 
									eachPer.getImpressions(), 
									eachPer.getClicks(), 
									MathematicsBasicOperationAgent.getDoubleByDivision(eachPer.getCtr(), 100.0, 4), 
									eachPer.getConversions(), 
									eachPer.getCostPerConversion(), 
									MathematicsBasicOperationAgent.getDoubleByDivision(eachPer.getConversionsFromInteractionsRate(), 100.0, 4),
									eachPer.getAverageCpc()
									)
							);
				}
			}
		} else {
				
		}
		
		
		return result;
	}//End