Periodic sync google spreadsheet data with datastore for excluded API

Test: go/vts-web-staging
Bug: 117810778
Change-Id: Ia08688edbe5ee97c3b57976d7cb6d4a100528755

17 files changed, 869 insertions, 100 deletions

diff --git a/build.gradle b/build.gradle
index 97d9b20..a58f9c7 100644
--- a/build.gradle
+++ b/build.gradle
@@ -5,10 +5,12 @@ buildscript {
     ext {
         springBootVersion = '1.5.13.RELEASE'
         objectifyVersion = '6.0'
+        jacksonVersion = '2.9.7'
         googleCloudVersion = '0.47.0-alpha'
         googleJavaFormatVersion = '0.7.1'
-        googleHttpClientVersion = '1.23.0'
+        googleHttpClientVersion = '1.25.0'
         appGradlePluginVersion = '2.0.0-rc3'
+        googleSheetsAPI = 'v4-rev548-1.25.0'
     }
     repositories {
         jcenter()
@@ -64,15 +66,21 @@ dependencies {
     compile group: 'com.google.code.gson', name: 'gson', version:'2.7'
     compile group: 'com.googlecode.objectify', name: 'objectify', version: "${objectifyVersion}"
     compile group: 'org.json', name: 'json', version:'20180130'
-    compile group: 'com.fasterxml.jackson.core', name: 'jackson-databind', version:'2.8.6'
-    compile(group: 'com.google.api-client', name: 'google-api-client', version:'1.23.0') {
-        exclude(module: 'guava-jdk5')
-    }
+    compile group: 'com.fasterxml.jackson.core', name: 'jackson-core', version: "${jacksonVersion}"
+    compile group: 'com.fasterxml.jackson.core', name: 'jackson-databind', version: "${jacksonVersion}"
+    compile group: 'com.fasterxml.jackson.core', name: 'jackson-annotations', version: "${jacksonVersion}"
+
     compile group: 'com.google.apis', name: 'google-api-services-oauth2', version:'v1-rev136-1.23.0'
     compile group: 'com.google.http-client', name: 'google-http-client', version: "${googleHttpClientVersion}"
     compile group: 'com.google.http-client', name: 'google-http-client-protobuf', version: "${googleHttpClientVersion}"
     compile group: 'com.google.visualization', name: 'visualization-datasource', version:'1.1.1'
 
+    compile(group: 'com.google.api-client', name: 'google-api-client', version: "${googleHttpClientVersion}") {
+        exclude(module: 'guava-jdk5')
+    }
+    compile group: 'com.google.oauth-client', name: 'google-oauth-client-jetty', version: "${googleHttpClientVersion}"
+    compile group: 'com.google.apis', name: 'google-api-services-sheets', version: "${googleSheetsAPI}"
+
     testCompile group: 'junit', name: 'junit', version: '4.12'
     testCompile group: 'org.mockito', name: 'mockito-core', version: '2.21.0'
     testCompile group: 'org.junit.jupiter', name: 'junit-jupiter-api', version:'5.0.3'
diff --git a/src/main/java/com/android/vts/config/ObjectifyListener.java b/src/main/java/com/android/vts/config/ObjectifyListener.java
index 66f1480..8beeda2 100644
--- a/src/main/java/com/android/vts/config/ObjectifyListener.java
+++ b/src/main/java/com/android/vts/config/ObjectifyListener.java
@@ -19,6 +19,7 @@ package com.android.vts.config;
 import com.android.vts.entity.ApiCoverageEntity;
 import com.android.vts.entity.BranchEntity;
 import com.android.vts.entity.BuildTargetEntity;
+import com.android.vts.entity.ApiCoverageExcludedEntity;
 import com.android.vts.entity.CodeCoverageEntity;
 import com.android.vts.entity.CoverageEntity;
 import com.android.vts.entity.DeviceInfoEntity;
@@ -53,10 +54,7 @@ import java.util.Properties;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 
-
-/**
- * The @WebListener annotation for registering a class as a listener of a web application.
- */
+/** The @WebListener annotation for registering a class as a listener of a web application. */
 @WebListener
 /**
  * Initializing Objectify Service at the container start up before any web components like servlet
@@ -64,97 +62,100 @@ import java.util.logging.Logger;
  */
 public class ObjectifyListener implements ServletContextListener {
 
-  private static final Logger logger = Logger.getLogger(ObjectifyListener.class.getName());
+    private static final Logger logger = Logger.getLogger(ObjectifyListener.class.getName());
 
-  /**
-   * Receives notification that the web application initialization process is starting. This
-   * function will register Entity classes for objectify.
-   */
-  @Override
-  public void contextInitialized(ServletContextEvent servletContextEvent) {
-    ObjectifyService.init();
+    /**
+     * Receives notification that the web application initialization process is starting. This
+     * function will register Entity classes for objectify.
+     */
+    @Override
+    public void contextInitialized(ServletContextEvent servletContextEvent) {
+        ObjectifyService.init();
         ObjectifyService.register(BranchEntity.class);
         ObjectifyService.register(BuildTargetEntity.class);
 
-    ObjectifyService.register(ApiCoverageEntity.class);
-    ObjectifyService.register(CodeCoverageEntity.class);
-    ObjectifyService.register(CoverageEntity.class);
-    ObjectifyService.register(DeviceInfoEntity.class);
-    ObjectifyService.register(TestCoverageStatusEntity.class);
-
-    ObjectifyService.register(ProfilingPointEntity.class);
-    ObjectifyService.register(ProfilingPointRunEntity.class);
-    ObjectifyService.register(ProfilingPointSummaryEntity.class);
-
-    ObjectifyService.register(TestEntity.class);
-    ObjectifyService.register(TestPlanEntity.class);
-    ObjectifyService.register(TestPlanRunEntity.class);
-    ObjectifyService.register(TestRunEntity.class);
-    ObjectifyService.register(TestCaseRunEntity.class);
-    ObjectifyService.register(TestStatusEntity.class);
-    ObjectifyService.register(TestSuiteFileEntity.class);
-    ObjectifyService.register(TestSuiteResultEntity.class);
-    ObjectifyService.register(RoleEntity.class);
-    ObjectifyService.register(UserEntity.class);
-    ObjectifyService.begin();
-    logger.log(Level.INFO, "Value Initialized from context.");
-
-    Properties systemConfigProp = new Properties();
-
-    try {
-      InputStream defaultInputStream =
-          ObjectifyListener.class
-              .getClassLoader()
-              .getResourceAsStream("config.properties");
-
-      systemConfigProp.load(defaultInputStream);
-
-      String roleList = systemConfigProp.getProperty("user.roleList");
-      Supplier<Stream<String>> streamSupplier = () -> Arrays.stream(roleList.split(","));
-      this.createRoles(streamSupplier.get());
-
-      String adminEmail = systemConfigProp.getProperty("user.adminEmail");
-      if (adminEmail.isEmpty()) {
-        logger.log(Level.WARNING, "Admin email is not properly set. Check config file");
-      } else {
-        String adminName = systemConfigProp.getProperty("user.adminName");
-        String adminCompany = systemConfigProp.getProperty("user.adminCompany");
-        Optional<String> roleName = streamSupplier.get().filter(r -> r.equals("admin")).findFirst();
-        this.createAdminUser(adminEmail, adminName, adminCompany, roleName.orElse("admin"));
-      }
-    } catch (FileNotFoundException e) {
-      e.printStackTrace();
-    } catch (IOException e) {
-      e.printStackTrace();
+        ObjectifyService.register(ApiCoverageEntity.class);
+        ObjectifyService.register(ApiCoverageExcludedEntity.class);
+        ObjectifyService.register(CodeCoverageEntity.class);
+        ObjectifyService.register(CoverageEntity.class);
+        ObjectifyService.register(DeviceInfoEntity.class);
+        ObjectifyService.register(TestCoverageStatusEntity.class);
+
+        ObjectifyService.register(ProfilingPointEntity.class);
+        ObjectifyService.register(ProfilingPointRunEntity.class);
+        ObjectifyService.register(ProfilingPointSummaryEntity.class);
+
+        ObjectifyService.register(TestEntity.class);
+        ObjectifyService.register(TestPlanEntity.class);
+        ObjectifyService.register(TestPlanRunEntity.class);
+        ObjectifyService.register(TestRunEntity.class);
+        ObjectifyService.register(TestCaseRunEntity.class);
+        ObjectifyService.register(TestStatusEntity.class);
+        ObjectifyService.register(TestSuiteFileEntity.class);
+        ObjectifyService.register(TestSuiteResultEntity.class);
+        ObjectifyService.register(RoleEntity.class);
+        ObjectifyService.register(UserEntity.class);
+        ObjectifyService.begin();
+        logger.log(Level.INFO, "Value Initialized from context.");
+
+        Properties systemConfigProp = new Properties();
+
+        try {
+            InputStream defaultInputStream =
+                    ObjectifyListener.class
+                            .getClassLoader()
+                            .getResourceAsStream("config.properties");
+
+            systemConfigProp.load(defaultInputStream);
+
+            String roleList = systemConfigProp.getProperty("user.roleList");
+            Supplier<Stream<String>> streamSupplier = () -> Arrays.stream(roleList.split(","));
+            this.createRoles(streamSupplier.get());
+
+            String adminEmail = systemConfigProp.getProperty("user.adminEmail");
+            if (adminEmail.isEmpty()) {
+                logger.log(Level.WARNING, "Admin email is not properly set. Check config file");
+            } else {
+                String adminName = systemConfigProp.getProperty("user.adminName");
+                String adminCompany = systemConfigProp.getProperty("user.adminCompany");
+                Optional<String> roleName =
+                        streamSupplier.get().filter(r -> r.equals("admin")).findFirst();
+                this.createAdminUser(adminEmail, adminName, adminCompany, roleName.orElse("admin"));
+            }
+        } catch (FileNotFoundException e) {
+            e.printStackTrace();
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+    }
+
+    /** Receives notification that the ServletContext is about to be shut down. */
+    @Override
+    public void contextDestroyed(ServletContextEvent servletContextEvent) {
+        ServletContext servletContext = servletContextEvent.getServletContext();
+        logger.log(Level.INFO, "Value deleted from context.");
+    }
+
+    private void createRoles(Stream<String> roleStream) {
+        roleStream
+                .map(role -> role.trim())
+                .forEach(
+                        roleName -> {
+                            RoleEntity roleEntity = new RoleEntity(roleName);
+                            roleEntity.save();
+                        });
     }
-  }
-
-  /**
-   * Receives notification that the ServletContext is about to be shut down.
-   */
-  @Override
-  public void contextDestroyed(ServletContextEvent servletContextEvent) {
-    ServletContext servletContext = servletContextEvent.getServletContext();
-    logger.log(Level.INFO, "Value deleted from context.");
-  }
-
-  private void createRoles(Stream<String> roleStream) {
-    roleStream.map(role -> role.trim()).forEach(roleName -> {
-      RoleEntity roleEntity = new RoleEntity(roleName);
-      roleEntity.save();
-    });
-  }
-
-  private void createAdminUser(String email, String name, String company, String role) {
-    Optional<UserEntity> adminUserEntityOptional = Optional
-        .ofNullable(UserEntity.getAdminUser(email));
-    if (adminUserEntityOptional.isPresent()) {
-      logger.log(Level.INFO, "The user is already registered.");
-    } else {
-      UserEntity userEntity = new UserEntity(email, name, company, role);
-      userEntity.setIsAdmin(true);
-      userEntity.save();
-      logger.log(Level.INFO, "The user is saved successfully.");
+
+    private void createAdminUser(String email, String name, String company, String role) {
+        Optional<UserEntity> adminUserEntityOptional =
+                Optional.ofNullable(UserEntity.getAdminUser(email));
+        if (adminUserEntityOptional.isPresent()) {
+            logger.log(Level.INFO, "The user is already registered.");
+        } else {
+            UserEntity userEntity = new UserEntity(email, name, company, role);
+            userEntity.setIsAdmin(true);
+            userEntity.save();
+            logger.log(Level.INFO, "The user is saved successfully.");
+        }
     }
-  }
 }
diff --git a/src/main/java/com/android/vts/entity/ApiCoverageExcludedEntity.java b/src/main/java/com/android/vts/entity/ApiCoverageExcludedEntity.java
new file mode 100644
index 0000000..834f8cc
--- /dev/null
+++ b/src/main/java/com/android/vts/entity/ApiCoverageExcludedEntity.java
@@ -0,0 +1,168 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.entity;
+
+import com.fasterxml.jackson.annotation.JsonAutoDetect;
+import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
+import com.googlecode.objectify.Key;
+import com.googlecode.objectify.annotation.Cache;
+import com.googlecode.objectify.annotation.Entity;
+import com.googlecode.objectify.annotation.Id;
+import com.googlecode.objectify.annotation.Index;
+import lombok.EqualsAndHashCode;
+import lombok.Getter;
+import lombok.NoArgsConstructor;
+import lombok.Setter;
+
+import java.util.Collection;
+import java.util.Date;
+import java.util.List;
+import java.util.concurrent.atomic.AtomicInteger;
+import java.util.stream.Collectors;
+
+import static com.googlecode.objectify.ObjectifyService.ofy;
+
+/**
+ * This entity class contain the excluded API information. And this information will be used to
+ * calculate more precise the ratio of API coverage.
+ */
+@Cache
+@Entity(name = "ApiCoverageExcluded")
+@EqualsAndHashCode(of = "id")
+@NoArgsConstructor
+@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
+@JsonIgnoreProperties({"id", "parent"})
+public class ApiCoverageExcludedEntity {
+
+    // The maximum number of entity list size to insert datastore
+    private static final int maxNumEntitySize = 500;
+
+    /** ApiCoverageEntity id field */
+    @Id @Getter @Setter private String id;
+
+    /** Package name. e.g. android.hardware.foo. */
+    @Index @Getter @Setter private String packageName;
+
+    /** Major Version. e.g. 1, 2. */
+    @Getter @Setter private int majorVersion;
+
+    /** Minor Version. e.g. 0. */
+    @Getter @Setter private int minorVersion;
+
+    /** Interface name. e.g. IFoo. */
+    @Index @Getter @Setter private String interfaceName;
+
+    /** API Name */
+    @Index @Getter @Setter private String apiName;
+
+    /** The reason comment for the excluded API */
+    @Getter @Setter private String comment;
+
+    /** When this record was created or updated */
+    @Index @Getter @Setter private Date updated;
+
+    /** Constructor function for ApiCoverageExcludedEntity Class */
+    public ApiCoverageExcludedEntity(
+            String packageName,
+            String version,
+            String interfaceName,
+            String apiName,
+            String comment) {
+
+        this.packageName = packageName;
+        this.interfaceName = interfaceName;
+        this.apiName = apiName;
+        this.comment = comment;
+
+        this.setVersions(version);
+    }
+
+    /** Setting major and minor version from version string */
+    private void setVersions(String version) {
+        String[] versionArray = version.split("[.]");
+        if (versionArray.length == 0) {
+            this.majorVersion = 0;
+            this.minorVersion = 0;
+        } else if (versionArray.length == 1) {
+            this.majorVersion = Integer.parseInt(versionArray[0]);
+            this.minorVersion = 0;
+        } else {
+            this.majorVersion = Integer.parseInt(versionArray[0]);
+            this.minorVersion = Integer.parseInt(versionArray[1]);
+        }
+    }
+
+    /** Getting objectify ID from the entity information */
+    private String getObjectifyId() {
+        return this.packageName
+                + "."
+                + this.majorVersion
+                + "."
+                + this.minorVersion
+                + "."
+                + this.interfaceName
+                + "."
+                + this.apiName;
+    }
+
+    /** Getting key from the entity */
+    public Key<ApiCoverageExcludedEntity> getKey() {
+        return Key.create(ApiCoverageExcludedEntity.class, this.getObjectifyId());
+    }
+
+    /** Saving function for the instance of this class */
+    public Key<ApiCoverageExcludedEntity> save() {
+        this.id = this.getObjectifyId();
+        this.updated = new Date();
+        return ofy().save().entity(this).now();
+    }
+
+    /** Spliting a list based on a given size */
+    public static <T> Collection<List<T>> partitionBasedOnSize(List<T> inputList, int size) {
+        final AtomicInteger counter = new AtomicInteger(0);
+        return inputList
+                .stream()
+                .collect(Collectors.groupingBy(s -> counter.getAndIncrement() / size))
+                .values();
+    }
+
+    /** Saving function with parameter of this entity List */
+    public static void saveAll(List<ApiCoverageExcludedEntity> apiCoverageExcludedEntityList) {
+        List<ApiCoverageExcludedEntity> entityWithIdList =
+                apiCoverageExcludedEntityList
+                        .stream()
+                        .map(
+                                entity -> {
+                                    entity.setId(entity.getObjectifyId());
+                                    entity.setUpdated(new Date());
+                                    return entity;
+                                })
+                        .collect(Collectors.toList());
+
+        partitionBasedOnSize(entityWithIdList, maxNumEntitySize)
+                .stream()
+                .forEach(
+                        entityList -> {
+                            ofy().save().entities(entityList).now();
+                        });
+    }
+
+    /** Get All Key List of ApiCoverageExcludedEntity */
+    public static List<Key<ApiCoverageExcludedEntity>> getAllKeyList() {
+        return ofy().load().type(ApiCoverageExcludedEntity.class).keys().list();
+    }
+}
diff --git a/src/main/java/com/android/vts/job/VtsSpreadSheetSyncServlet.java b/src/main/java/com/android/vts/job/VtsSpreadSheetSyncServlet.java
new file mode 100644
index 0000000..079aa2d
--- /dev/null
+++ b/src/main/java/com/android/vts/job/VtsSpreadSheetSyncServlet.java
@@ -0,0 +1,165 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.job;
+
+import com.android.vts.entity.ApiCoverageExcludedEntity;
+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;
+import com.google.api.services.sheets.v4.model.ValueRange;
+
+import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.security.GeneralSecurityException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+import javax.servlet.ServletConfig;
+import javax.servlet.ServletException;
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/** Job to sync excluded API data in google spreadsheet with datastore's entity. */
+public class VtsSpreadSheetSyncServlet extends BaseJobServlet {
+
+    protected static final Logger logger =
+            Logger.getLogger(VtsSpreadSheetSyncServlet.class.getName());
+
+    private static final String APPLICATION_NAME = "VTS Dashboard";
+    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
+    private static final String TOKENS_DIRECTORY_PATH = "tokens";
+
+    /**
+     * Global instance of the scopes. If modifying these scopes, delete your previously saved
+     * tokens/ folder.
+     */
+    private static final List<String> SCOPES =
+            Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
+
+    private String CREDENTIALS_KEY_FILE = "";
+
+    /** GoogleClientSecrets for GoogleAuthorizationCodeFlow Builder */
+    private GoogleClientSecrets clientSecrets;
+
+    /** This is the ID of google spreadsheet. */
+    private String SPREAD_SHEET_ID = "";
+
+    /** This is the range to read of google spreadsheet. */
+    private String SPREAD_SHEET_RANGE = "";
+
+    @Override
+    public void init(ServletConfig servletConfig) throws ServletException {
+        super.init(servletConfig);
+
+        try {
+            CREDENTIALS_KEY_FILE = systemConfigProp.getProperty("api.coverage.keyFile");
+            SPREAD_SHEET_ID = systemConfigProp.getProperty("api.coverage.spreadSheetId");
+            SPREAD_SHEET_RANGE = systemConfigProp.getProperty("api.coverage.spreadSheetRange");
+
+            InputStream keyFileInputStream =
+                    this.getClass()
+                            .getClassLoader()
+                            .getResourceAsStream("keys/" + CREDENTIALS_KEY_FILE);
+            InputStreamReader keyFileStreamReader = new InputStreamReader(keyFileInputStream);
+
+            this.clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, keyFileStreamReader);
+        } catch (IOException ioe) {
+            logger.log(Level.SEVERE, ioe.getMessage());
+        } catch (Exception exception) {
+            logger.log(Level.SEVERE, exception.getMessage());
+        }
+    }
+
+    /**
+     * 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 Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
+
+        // Build flow and trigger user authorization request.
+        File fileTokenDirPath = new File(TOKENS_DIRECTORY_PATH);
+        FileDataStoreFactory fileDataStoreFactory = new FileDataStoreFactory(fileTokenDirPath);
+
+        GoogleAuthorizationCodeFlow flow =
+                new GoogleAuthorizationCodeFlow.Builder(
+                                HTTP_TRANSPORT, JSON_FACTORY, this.clientSecrets, SCOPES)
+                        .setDataStoreFactory(fileDataStoreFactory)
+                        .setAccessType("offline")
+                        .build();
+        LocalServerReceiver localServerReceiver = new LocalServerReceiver();
+        return new AuthorizationCodeInstalledApp(flow, localServerReceiver).authorize("user");
+    }
+
+    @Override
+    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
+
+        try {
+            // 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();
+
+            ValueRange valueRange =
+                    service.spreadsheets()
+                            .values()
+                            .get(SPREAD_SHEET_ID, SPREAD_SHEET_RANGE)
+                            .execute();
+
+            List<ApiCoverageExcludedEntity> apiCoverageExcludedEntities = new ArrayList<>();
+            List<List<Object>> values = valueRange.getValues();
+            if (values == null || values.isEmpty()) {
+                logger.log(Level.WARNING, "No data found in google spreadsheet.");
+            } else {
+                for (List row : values) {
+                    ApiCoverageExcludedEntity apiCoverageExcludedEntity =
+                            new ApiCoverageExcludedEntity(
+                                    row.get(0).toString(),
+                                    row.get(1).toString(),
+                                    row.get(2).toString(),
+                                    row.get(3).toString(),
+                                    row.get(4).toString());
+                    apiCoverageExcludedEntities.add(apiCoverageExcludedEntity);
+                }
+            }
+
+            ApiCoverageExcludedEntity.saveAll(apiCoverageExcludedEntities);
+
+        } catch (GeneralSecurityException gse) {
+            logger.log(Level.SEVERE, gse.getMessage());
+        }
+    }
+}
diff --git a/src/main/resources/config.properties b/src/main/resources/config.properties
index 20c4ac2..5d56f08 100644
--- a/src/main/resources/config.properties
+++ b/src/main/resources/config.properties
@@ -17,6 +17,10 @@ gcs.bucketName=
 gcs.infraLogBucketName=
 gcs.suiteTestFolderName=
 
+api.coverage.keyFile=
+api.coverage.spreadSheetId=
+api.coverage.spreadSheetRange=
+
 bug.tracking.system=
 
 user.adminEmail=
diff --git a/src/main/webapp/WEB-INF/cron.xml b/src/main/webapp/WEB-INF/cron.xml
index 2dc72f8..78beefb 100644
--- a/src/main/webapp/WEB-INF/cron.xml
+++ b/src/main/webapp/WEB-INF/cron.xml
@@ -33,4 +33,10 @@ Copyright 2016 Google Inc. All Rights Reserved.
     <schedule>every 5 mins</schedule>
     <timezone>America/Los_Angeles</timezone>
   </cron>
+  <cron>
+    <url>/cron/vts_spreadsheet_sync_job</url>
+    <description>Sync google spreadsheet data with datastore entity.</description>
+    <schedule>every day 00:30</schedule>
+    <timezone>America/Los_Angeles</timezone>
+  </cron>
 </cronentries>
\ No newline at end of file
diff --git a/src/main/webapp/WEB-INF/web.xml b/src/main/webapp/WEB-INF/web.xml
index 3942fe4..a683f48 100644
--- a/src/main/webapp/WEB-INF/web.xml
+++ b/src/main/webapp/WEB-INF/web.xml
@@ -176,6 +176,11 @@ Copyright 2016 Google Inc. All Rights Reserved.
 </servlet>
 
 <servlet>
+  <servlet-name>vts_spreadsheet_sync_job</servlet-name>
+  <servlet-class>com.android.vts.job.VtsSpreadSheetSyncServlet</servlet-class>
+</servlet>
+
+<servlet>
   <servlet-name>suite_test_report_gcs_monitor_job</servlet-name>
   <servlet-class>com.android.vts.job.VtsSuiteTestJobServlet</servlet-class>
 </servlet>
@@ -326,6 +331,11 @@ Copyright 2016 Google Inc. All Rights Reserved.
 </servlet-mapping>
 
 <servlet-mapping>
+  <servlet-name>vts_spreadsheet_sync_job</servlet-name>
+  <url-pattern>/cron/vts_spreadsheet_sync_job/*</url-pattern>
+</servlet-mapping>
+
+<servlet-mapping>
   <servlet-name>suite_test_report_gcs_monitor_job</servlet-name>
   <url-pattern>/cron/test_suite_report_gcs_monitor/*</url-pattern>
 </servlet-mapping>
diff --git a/src/test/java/com/android/vts/api/VtsSpreadSheetSyncServletTest.java b/src/test/java/com/android/vts/api/VtsSpreadSheetSyncServletTest.java
new file mode 100644
index 0000000..f6e3694
--- /dev/null
+++ b/src/test/java/com/android/vts/api/VtsSpreadSheetSyncServletTest.java
@@ -0,0 +1,87 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.api;
+
+import com.android.vts.entity.ApiCoverageExcludedEntity;
+import com.android.vts.job.VtsSpreadSheetSyncServlet;
+import com.android.vts.util.ObjectifyTestBase;
+import com.google.gson.Gson;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.mockito.Mock;
+
+import javax.servlet.ServletConfig;
+import javax.servlet.ServletException;
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.PrintWriter;
+import java.io.StringWriter;
+import java.util.List;
+
+import static com.googlecode.objectify.ObjectifyService.factory;
+import static com.googlecode.objectify.ObjectifyService.ofy;
+import static org.junit.Assert.assertEquals;
+import static org.mockito.Mockito.when;
+
+public class VtsSpreadSheetSyncServletTest extends ObjectifyTestBase {
+
+    private Gson gson;
+
+    @Mock private HttpServletRequest request;
+
+    @Mock private HttpServletResponse response;
+
+    @Mock ServletConfig servletConfig;
+
+    /** It be executed before each @Test method */
+    @BeforeEach
+    void setUpExtra() {
+        gson = new Gson();
+    }
+
+    @Test
+    public void testSyncServletJob() throws IOException, ServletException {
+
+        factory().register(ApiCoverageExcludedEntity.class);
+
+        when(request.getPathInfo()).thenReturn("/cron/vts_spreadsheet_sync_job");
+
+        StringWriter sw = new StringWriter();
+        PrintWriter pw = new PrintWriter(sw);
+
+        when(response.getWriter()).thenReturn(pw);
+
+        VtsSpreadSheetSyncServlet vtsSpreadSheetSyncServlet = new VtsSpreadSheetSyncServlet();
+        vtsSpreadSheetSyncServlet.init(servletConfig);
+        vtsSpreadSheetSyncServlet.doGet(request, response);
+        String result = sw.getBuffer().toString().trim();
+
+        List<ApiCoverageExcludedEntity> apiCoverageExcludedEntityList =
+                ofy().load().type(ApiCoverageExcludedEntity.class).list();
+
+        assertEquals(apiCoverageExcludedEntityList.size(), 2);
+        assertEquals(apiCoverageExcludedEntityList.get(0).getApiName(), "getMasterMuteTest");
+        assertEquals(
+                apiCoverageExcludedEntityList.get(0).getPackageName(),
+                "android.hardware.audio.test");
+        assertEquals(apiCoverageExcludedEntityList.get(1).getApiName(), "getMasterVolumeTest");
+        assertEquals(
+                apiCoverageExcludedEntityList.get(1).getPackageName(),
+                "android.hardware.video.test");
+    }
+}
diff --git a/src/test/java/com/android/vts/entity/ApiCoverageExcludedEntityTest.java b/src/test/java/com/android/vts/entity/ApiCoverageExcludedEntityTest.java
new file mode 100644
index 0000000..4fa9b2a
--- /dev/null
+++ b/src/test/java/com/android/vts/entity/ApiCoverageExcludedEntityTest.java
@@ -0,0 +1,62 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.entity;
+
+import com.android.vts.util.ObjectifyTestBase;
+import com.googlecode.objectify.Key;
+import org.junit.jupiter.api.Test;
+
+import static com.googlecode.objectify.ObjectifyService.factory;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+public class ApiCoverageExcludedEntityTest extends ObjectifyTestBase {
+
+    @Test
+    public void saveTest() {
+        factory().register(ApiCoverageExcludedEntity.class);
+
+        String packageName = "android.hardware.audio";
+        String apiName = "createTestPatch";
+        String version = "2.1";
+        ApiCoverageExcludedEntity apiCoverageExcludedEntity =
+                new ApiCoverageExcludedEntity(
+                        packageName, version, "IDevice", apiName, "not testable");
+        apiCoverageExcludedEntity.save();
+
+        assertEquals(apiCoverageExcludedEntity.getPackageName(), packageName);
+        assertEquals(apiCoverageExcludedEntity.getApiName(), apiName);
+        assertEquals(apiCoverageExcludedEntity.getMajorVersion(), 2);
+        assertEquals(apiCoverageExcludedEntity.getMinorVersion(), 1);
+    }
+
+    @Test
+    public void getUrlSafeKeyTest() {
+        factory().register(ApiCoverageExcludedEntity.class);
+
+        Key testParentKey = Key.create(TestEntity.class, "test1");
+        Key testRunParentKey = Key.create(testParentKey, TestRunEntity.class, 1);
+
+        CodeCoverageEntity codeCoverageEntity =
+                new CodeCoverageEntity(testRunParentKey, 1000, 3500);
+        codeCoverageEntity.save();
+
+        String urlKey =
+                "kind%3A+%22Test%22%0A++name%3A+%22test1%22%0A%7D%0Apath+%7B%0A++kind%3A+%22TestRun%22%0A++id%3A+1%0A%7D%0Apath+%7B%0A++kind%3A+%22CodeCoverage%22%0A++id%3A+1%0A%7D%0A";
+        assertTrue(codeCoverageEntity.getUrlSafeKey().endsWith(urlKey));
+    }
+}
diff --git a/src/test/java/com/android/vts/entity/CodeCoverageEntityTest.java b/src/test/java/com/android/vts/entity/CodeCoverageEntityTest.java
index 47631c0..06cad28 100644
--- a/src/test/java/com/android/vts/entity/CodeCoverageEntityTest.java
+++ b/src/test/java/com/android/vts/entity/CodeCoverageEntityTest.java
@@ -1,12 +1,25 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
 package com.android.vts.entity;
 
 import com.android.vts.util.ObjectifyTestBase;
-import com.google.appengine.api.datastore.Entity;
 import com.googlecode.objectify.Key;
 import org.junit.jupiter.api.Test;
 
-import java.util.Arrays;
-import java.util.List;
 
 import static com.googlecode.objectify.ObjectifyService.factory;
 import static org.junit.Assert.assertEquals;
diff --git a/src/test/java/com/android/vts/entity/CodeCoverageFileEntityTest.java b/src/test/java/com/android/vts/entity/CodeCoverageFileEntityTest.java
index 1bbdd9e..5c944b2 100644
--- a/src/test/java/com/android/vts/entity/CodeCoverageFileEntityTest.java
+++ b/src/test/java/com/android/vts/entity/CodeCoverageFileEntityTest.java
@@ -1,3 +1,19 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
 package com.android.vts.entity;
 
 import com.android.vts.util.ObjectifyTestBase;
diff --git a/src/test/java/com/android/vts/util/LocalDatastoreExtension.java b/src/test/java/com/android/vts/util/LocalDatastoreExtension.java
new file mode 100644
index 0000000..b06303c
--- /dev/null
+++ b/src/test/java/com/android/vts/util/LocalDatastoreExtension.java
@@ -0,0 +1,61 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.util;
+
+import com.google.cloud.datastore.testing.LocalDatastoreHelper;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.junit.jupiter.api.extension.BeforeAllCallback;
+import org.junit.jupiter.api.extension.BeforeEachCallback;
+import org.junit.jupiter.api.extension.ExtensionContext;
+import org.junit.jupiter.api.extension.ExtensionContext.Namespace;
+
+/** Sets up and tears down the Local Datastore emulator, defaults to strong consistency */
+@RequiredArgsConstructor
+@Slf4j
+public class LocalDatastoreExtension implements BeforeAllCallback, BeforeEachCallback {
+
+    private final double consistency;
+
+    public LocalDatastoreExtension() {
+        this(1.0);
+    }
+
+    @Override
+    public void beforeAll(final ExtensionContext context) throws Exception {
+        if (getHelper(context) == null) {
+            log.info("Creating new LocalDatastoreHelper");
+
+            final LocalDatastoreHelper helper = LocalDatastoreHelper.create(consistency);
+            context.getRoot().getStore(Namespace.GLOBAL).put(LocalDatastoreHelper.class, helper);
+            helper.start();
+        }
+    }
+
+    @Override
+    public void beforeEach(final ExtensionContext context) throws Exception {
+        final LocalDatastoreHelper helper = getHelper(context);
+        helper.reset();
+    }
+
+    /** Get the helper created in beforeAll; it should be global so there will one per test run */
+    public static LocalDatastoreHelper getHelper(final ExtensionContext context) {
+        return context.getRoot()
+                .getStore(Namespace.GLOBAL)
+                .get(LocalDatastoreHelper.class, LocalDatastoreHelper.class);
+    }
+}
diff --git a/src/test/java/com/android/vts/util/MockitoExtension.java b/src/test/java/com/android/vts/util/MockitoExtension.java
new file mode 100644
index 0000000..821574f
--- /dev/null
+++ b/src/test/java/com/android/vts/util/MockitoExtension.java
@@ -0,0 +1,34 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.util;
+
+import org.junit.jupiter.api.extension.BeforeEachCallback;
+import org.junit.jupiter.api.extension.ExtensionContext;
+import org.mockito.MockitoAnnotations;
+
+/**
+ * This will enable mockito annotations programmatically, by invoking MockitoAnnotations.initMocks()
+ */
+public class MockitoExtension implements BeforeEachCallback {
+
+    @Override
+    public void beforeEach(final ExtensionContext context) throws Exception {
+        final Object testInstance = context.getTestInstance().get();
+
+        MockitoAnnotations.initMocks(testInstance);
+    }
+}
diff --git a/src/test/java/com/android/vts/util/ObjectifyExtension.java b/src/test/java/com/android/vts/util/ObjectifyExtension.java
new file mode 100644
index 0000000..a60ba0e
--- /dev/null
+++ b/src/test/java/com/android/vts/util/ObjectifyExtension.java
@@ -0,0 +1,52 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.util;
+
+import com.google.cloud.datastore.Datastore;
+import com.googlecode.objectify.ObjectifyFactory;
+import com.googlecode.objectify.ObjectifyService;
+import com.googlecode.objectify.util.Closeable;
+import org.junit.jupiter.api.extension.AfterEachCallback;
+import org.junit.jupiter.api.extension.BeforeEachCallback;
+import org.junit.jupiter.api.extension.ExtensionContext;
+import org.junit.jupiter.api.extension.ExtensionContext.Namespace;
+
+/** Sets up and tears down the GAE local unit test harness environment */
+public class ObjectifyExtension implements BeforeEachCallback, AfterEachCallback {
+
+    private static final Namespace NAMESPACE = Namespace.create(ObjectifyExtension.class);
+
+    @Override
+    public void beforeEach(final ExtensionContext context) throws Exception {
+        final Datastore datastore =
+                LocalDatastoreExtension.getHelper(context).getOptions().getService();
+
+        ObjectifyService.init(new ObjectifyFactory(datastore));
+
+        final Closeable rootService = ObjectifyService.begin();
+
+        context.getStore(NAMESPACE).put(Closeable.class, rootService);
+    }
+
+    @Override
+    public void afterEach(final ExtensionContext context) throws Exception {
+        final Closeable rootService =
+                context.getStore(NAMESPACE).get(Closeable.class, Closeable.class);
+
+        rootService.close();
+    }
+}
diff --git a/src/test/java/com/android/vts/util/ObjectifyTestBase.java b/src/test/java/com/android/vts/util/ObjectifyTestBase.java
new file mode 100644
index 0000000..0b3b5e8
--- /dev/null
+++ b/src/test/java/com/android/vts/util/ObjectifyTestBase.java
@@ -0,0 +1,78 @@
+/*
+ * Copyright (C) 2018 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License"); you
+ * may not use this file except in compliance with the License. You may
+ * obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
+ * implied. See the License for the specific language governing
+ * permissions and limitations under the License.
+ */
+
+package com.android.vts.util;
+
+import com.google.cloud.datastore.Datastore;
+import com.google.cloud.datastore.EntityValue;
+import com.google.cloud.datastore.FullEntity;
+import com.google.cloud.datastore.IncompleteKey;
+import com.google.cloud.datastore.Value;
+import com.googlecode.objectify.Key;
+import com.googlecode.objectify.cache.MemcacheService;
+import com.googlecode.objectify.impl.AsyncDatastore;
+import org.junit.jupiter.api.extension.ExtendWith;
+
+import static com.googlecode.objectify.ObjectifyService.factory;
+import static com.googlecode.objectify.ObjectifyService.ofy;
+
+/** All tests should extend this class to set up the GAE environment. */
+@ExtendWith({
+    MockitoExtension.class,
+    LocalDatastoreExtension.class,
+    ObjectifyExtension.class,
+})
+public class ObjectifyTestBase {
+    /** Set embedded entity with property name */
+    protected Value<FullEntity<?>> makeEmbeddedEntityWithProperty(
+            final String name, final Value<?> value) {
+        return EntityValue.of(FullEntity.newBuilder().set(name, value).build());
+    }
+
+    /** Get datastore instance */
+    protected Datastore datastore() {
+        return factory().datastore();
+    }
+
+    /** Get memcache instance */
+    protected MemcacheService memcache() {
+        return factory().memcache();
+    }
+
+    /** Get asynchronous datastore instance */
+    protected AsyncDatastore asyncDatastore() {
+        return factory().asyncDatastore();
+    }
+
+    /** Save an entity and clear cache data and return entity by finding the key */
+    protected <E> E saveClearLoad(final E thing) {
+        final Key<E> key = ofy().save().entity(thing).now();
+        ofy().clear();
+        return ofy().load().key(key).now();
+    }
+
+    /** Get the entity instance from class type */
+    protected FullEntity.Builder<?> makeEntity(final Class<?> kind) {
+        return makeEntity(Key.getKind(kind));
+    }
+
+    /** Get the entity instance from class name */
+    protected FullEntity.Builder<?> makeEntity(final String kind) {
+        final IncompleteKey incompleteKey =
+                factory().datastore().newKeyFactory().setKind(kind).newKey();
+        return FullEntity.newBuilder(incompleteKey);
+    }
+}
diff --git a/src/test/resources/config.properties b/src/test/resources/config.properties
index 745065b..2368374 100644
--- a/src/test/resources/config.properties
+++ b/src/test/resources/config.properties
@@ -1,2 +1,6 @@
 
-testProjectID=
\ No newline at end of file
+testProjectID=
+
+api.coverage.keyFile=
+api.coverage.spreadSheetId=
+api.coverage.spreadSheetRange=
diff --git a/src/test/resources/data/android_vts.vts_api_coverage_exlude_test.ods b/src/test/resources/data/android_vts.vts_api_coverage_exlude_test.ods
new file mode 100644
index 0000000..eb557d0
--- /dev/null
+++ b/src/test/resources/data/android_vts.vts_api_coverage_exlude_test.ods