oauth - 使用 2 Legged OAUTH/OPEN ID 域帐户使用 Google GData API 批量更新电子表格

Question

好的，周末就这样结束了——这是我周日晚上最后一次求助。

必须使用 2 Legged OAUTH 方法的 Google Marketplace 应用程序需要将 50K 条记录写入 Google Doc 电子表格。我能够创建、调整大小、重命名记录并将记录写入电子表格。单独写入 100 行最多需要一分钟，因此我必须使用批量更新。无法进行批量更新。下面的代码是我最好的尝试 - 我不断收到“ token 无效 - 无效 token 401”。所有示例代码都适用于三足 oauth - 但我无法提示用户批准该应用程序，因为它是一个市场应用程序

如果能找到一个使用 openid 和 2LO 批量更新电子表格的例子就好了

      final String SCOPE = "https://spreadsheets.google.com/feeds/spreadsheets/private/full";
        SpreadsheetService spreadsheetService;
        String consumerKey = getInitParameter("consumer_key");
        String consumerSecret = getInitParameter("consumer_secret");
        GoogleOAuthParameters oauthParameters = new GoogleOAuthParameters();
        oauthParameters.setOAuthConsumerKey(consumerKey);
        oauthParameters.setOAuthConsumerSecret(consumerSecret);
        oauthParameters.setOAuthType(OAuthParameters.OAuthType.TWO_LEGGED_OAUTH);
        oauthParameters.setScope(SCOPE);
        OAuthSigner signer = new OAuthHmacSha1Signer();
        spreadsheetService = new SpreadsheetService("nimbits-com");
        String title = entity.getName().getValue();

        try {

            spreadsheetService.setOAuthCredentials(oauthParameters, signer);
            spreadsheetService.setProtocolVersion(SpreadsheetService.Versions.V3);
            SpreadsheetQuery query = new SpreadsheetQuery(new URL(SCOPE));
            query.addCustomParameter(new Query.CustomParameter("xoauth_requestor_id", user.getEmail().getValue()));
            query.setTitleQuery(title);
            SpreadsheetFeed feed =  spreadsheetService.query(query, SpreadsheetFeed.class);


//works fine up to this point, I get the feed and spreadsheet.

            if (feed != null && ! feed.getEntries().isEmpty()) {

                com.google.gdata.data.spreadsheet.SpreadsheetEntry wsEntry = feed.getEntries().get(0);
                WorksheetEntry sheet = wsEntry.getWorksheets().get(0);
                CellFeed batchRequest = new CellFeed();
                String batchId = "R" + 2 + "C" + 1;


                URL entryUrl = new URL(sheet.getCellFeedUrl().toString() + "/" + batchId);

//Invalid TOKEN error here, trying to get the entry. 
CellEntry batchOperation = spreadsheetService.getEntry(entryUrl, CellEntry.class);
                batchOperation.setService(spreadsheetService);
                batchOperation.changeInputValueLocal("test");
                BatchUtils.setBatchId(batchOperation, batchId);
                BatchUtils.setBatchOperationType(batchOperation, BatchOperationType.UPDATE);
                batchRequest.getEntries().add(batchOperation);


                CellFeed cellFeed = spreadsheetService.getFeed(sheet.getCellFeedUrl(), CellFeed.class);
                Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
                URL batchUrl = new URL(batchLink.getHref());
                spreadsheetService.batch(batchUrl, batchRequest);
            }
        } catch (MalformedURLException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);

        } catch (ServiceException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        } catch (IOException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        } catch (OAuthException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        }

此示例只是尝试批量写入一条记录。我已经调整了电子表格的大小，它看起来也不错。我看不到如何设置 token 或使用 2LO 完成此请求。我试过将 xoauth_requestor_id 添加到 url。

自从我开始赏金后编辑:

这段代码完美运行，并使用我拥有的 2LO 身份验证将 1 行添加到现有电子表格。我需要修改它以快速插入 50,000 行。

 @Override
public void addSpreadsheetHeader(Entity entity) throws NimbitsException {
    final User user = UserServiceFactory.getServerInstance().getHttpRequestUser(
            this.getThreadLocalRequest());


    SpreadsheetService spreadsheetService;
    String consumerKey = getInitParameter("consumer_key");
    String consumerSecret = getInitParameter("consumer_secret");
    GoogleOAuthParameters oauthParameters = new GoogleOAuthParameters();
    oauthParameters.setOAuthConsumerKey(consumerKey);
    oauthParameters.setOAuthConsumerSecret(consumerSecret);
    spreadsheetService = new SpreadsheetService("nimbits-com");


    //  SpreadsheetEntry entry = new SpreadsheetEntry();
    String title = entity.getName().getValue();
    //  entry.setTitle(TextConstruct.plainText(title));



    try {
        spreadsheetService.setOAuthCredentials(oauthParameters, new OAuthHmacSha1Signer());
        SpreadsheetQuery query = new SpreadsheetQuery(new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full"));
        query.addCustomParameter(new Query.CustomParameter("xoauth_requestor_id", user.getEmail().getValue()));

        query.setTitleQuery(title);
        SpreadsheetFeed feed =  spreadsheetService.query(query, SpreadsheetFeed.class);
        if (feed != null && ! feed.getEntries().isEmpty()) {
            com.google.gdata.data.spreadsheet.SpreadsheetEntry wsEntry = feed.getEntries().get(0);
            WorksheetEntry sheet = wsEntry.getWorksheets().get(0);
            URL cellFeedUrl= sheet.getCellFeedUrl ();
            CellFeed cellFeed= spreadsheetService.getFeed (cellFeedUrl, CellFeed.class);
            CellEntry cellEntry;

            cellEntry= new CellEntry (1, 1, "Timestamp");
            cellFeed.insert (cellEntry);

            cellEntry= new CellEntry (1, 2, "Value");
            cellFeed.insert (cellEntry);

            cellEntry= new CellEntry (1, 3, "Latitude");
            cellFeed.insert (cellEntry);

            cellEntry= new CellEntry (1, 4, "Longitude");
            cellFeed.insert (cellEntry);

            cellEntry= new CellEntry (1, 5, "Annotation");
            cellFeed.insert (cellEntry);

            cellEntry= new CellEntry (1, 6, "Data");
            cellFeed.insert (cellEntry);

        }



    } catch (MalformedURLException e) {
        LogHelper.logException(this.getClass(), e);
        throw new NimbitsException(e);

    } catch (ServiceException e) {
        LogHelper.logException(this.getClass(), e);
        throw new NimbitsException(e);
    } catch (IOException e) {
        LogHelper.logException(this.getClass(), e);
        throw new NimbitsException(e);
    } catch (OAuthException e) {
        LogHelper.logException(this.getClass(), e);
        throw new NimbitsException(e);
    }


}

Answer 1

这里有很多未记录的小东西 - 但两条腿的身份验证转储到电子表格:

从各种来源中提取了一些示例代码并将其组合在一起。文档键来自创建新文档时返回的对象 .getId。

  @Override
    public void dumpValues(final Entity entity, int count) throws NimbitsException {
        String[][] values = {   {"1", "2", "3", "4", "5"},
                {"a", "b", "c", "d", "e"},
                {"dummy", "foo", "bar", "x", "y"}};


        final User user = //where you store your user


        SpreadsheetService spreadsheetService;
        String consumerKey = getInitParameter("consumer_key");
        String consumerSecret = getInitParameter("consumer_secret");
        GoogleOAuthParameters oauthParameters = new GoogleOAuthParameters();
        oauthParameters.setOAuthConsumerKey(consumerKey);
        oauthParameters.setOAuthConsumerSecret(consumerSecret);
        spreadsheetService = new SpreadsheetService("nimbits-com");
        spreadsheetService.setProtocolVersion(SpreadsheetService.Versions.V1);


        try {
            spreadsheetService.setOAuthCredentials(oauthParameters, new OAuthHmacSha1Signer());
            String key = String.valueOf(this.getThreadLocalRequest().getSession().getAttribute("docId"));
            FeedURLFactory urlFactory = FeedURLFactory.getDefault();
            URL cellFeedUrl = urlFactory.getCellFeedUrl(key, "od6", "private", "full");


            CellQuery q = new CellQuery(cellFeedUrl);
            //CellQuery q = new CellQuery(worksheet.getCellFeedUrl());
            q.setMinimumRow(1);
            q.setMaximumRow(1 + values.length);
            q.setMinimumCol(1);
            q.setMaximumCol(values[0].length);
            q.setReturnEmpty(true);
            q.addCustomParameter(new Query.CustomParameter("xoauth_requestor_id", user.getEmail().getValue()));
            CellFeed cellFeed = spreadsheetService.query(q, CellFeed.class);

            CellFeed batchRequestFeed = new CellFeed();

            // set values for each cell
            int currentCellEntry=0;
            for (int i=0; i < values.length; i++) {
                for (int j=0; j < values[i].length; j++) {

                    CellEntry entry = new CellEntry(cellFeed.getEntries().get(currentCellEntry));
                    entry.changeInputValueLocal(values[i][j]);
                    BatchUtils.setBatchId(entry, (new Integer(currentCellEntry)).toString());
                    BatchUtils.setBatchOperationType(entry, BatchOperationType.UPDATE);
                    batchRequestFeed.getEntries().add(entry);
                    currentCellEntry++;
                }
            }

            // upload cells
            Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
            spreadsheetService.setHeader("If-Match", "*");

            CellFeed batchResponse = spreadsheetService.batch(new URL(batchLink.getHref() ), batchRequestFeed);
            spreadsheetService.setHeader("If-Match", null);
            for (CellEntry entry : batchResponse.getEntries()) {
                if (!BatchUtils.isSuccess(entry)) {

                    BatchStatus status = BatchUtils.getBatchStatus(entry);
                    throw new NimbitsException(BatchUtils.getBatchId(entry) + " " + status.getReason() + " " + status.getContent());
                }
            }
        } catch (IOException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        } catch (ServiceException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        } catch (OAuthException e) {
            LogHelper.logException(this.getClass(), e);
            throw new NimbitsException(e);
        }