lib/Processors/GoogleSheetProcessor.rb in ZReviewTender-1.3.5 vs lib/Processors/GoogleSheetProcessor.rb in ZReviewTender-1.3.6

@@ -6,11 +6,11 @@
 require "pathname"
 require "GoogleAPI"
 
 class GoogleSheetProcessor < Processor
 
-    attr_accessor :keywordsInclude, :ratingsInclude, :territoriesInclude, :logger, :googleAPI, :sheetID, :sheetName, :formatValues, :timeZoneOffset
+    attr_accessor :keywordsInclude, :ratingsInclude, :territoriesInclude, :logger, :googleAPI, :spreadsheetID, :sheetInsertType, :sheetId, :sheetName, :sheetInsertAt, :formatValues, :timeZoneOffset
 
     def initialize(config, configFilePath, baseExecutePath)
         @config = config
         @configFilePath = configFilePath
         @baseExecutePath = baseExecutePath
@@ -39,12 +39,35 @@
         if !config["territoriesInclude"].nil?
             @territoriesInclude = config["territoriesInclude"]
         end
 
         @timeZoneOffset = Helper.unwrapRequiredParameter(config, "googleSheetTimeZoneOffset")
-        @sheetID = Helper.unwrapRequiredParameter(config, "googleSheetID")
-        @sheetName = Helper.unwrapRequiredParameter(config, "googleSheetName")
+        @spreadsheetID = Helper.unwrapRequiredParameter(config, "googleSpreadsheetID")
+        
+        sheetInsertStyle = Helper.unwrapRequiredParameter(config, "googleSheetInsertStyle")
+        if !sheetInsertStyle.is_a? Array
+            raise "googleSheetInsertStyle must specify as Array in GoogleSheetProcessor."
+        end
+
+        sheetInsertStyles = {}
+        sheetInsertStyle.each do |value|
+            value.keys.each do |key|
+                sheetInsertStyles[key] = value[key]
+            end
+        end
+
+        @sheetInsertType = Helper.unwrapRequiredParameter(sheetInsertStyles, "type")
+
+        if sheetInsertType != "insert" && sheetInsertType != "append"
+            raise "googleSheetInsertStyle.type only accept insert or append in GoogleSheetProcessor."
+        elsif sheetInsertType == "insert"
+            @sheetInsertAt = Helper.unwrapRequiredParameter(sheetInsertStyles, "at").to_i
+            @sheetId = Helper.unwrapRequiredParameter(sheetInsertStyles, "sheetID")
+        elsif sheetInsertType == "append"
+            @sheetName = Helper.unwrapRequiredParameter(sheetInsertStyles, "sheetName")
+        end
+        
         @formatValues = []
         if !config["values"].nil?
             @formatValues = config["values"]
         end
 
@@ -72,11 +95,16 @@
                 filterReviews = filterReviews.select{ |review| review.body.include? keywordInclude }
             end
         end
 
         values = []
-        filterReviews.each do |review|
+        sortedFilterReviews = filterReviews
+        if sheetInsertType == "insert"
+            sortedFilterReviews = sortedFilterReviews.sort! { |a, b|  b.createdDateTimestamp <=> a.createdDateTimestamp }
+        end
+
+        sortedFilterReviews.each do |review|
             cols = []
             formatValues.each do |formatValue|
                 formatValue = formatValue.gsub("%TITLE%", review.title || "")
                 formatValue = formatValue.gsub("%BODY%", review.body || "")
                 formatValue = formatValue.gsub("%RATING%", review.rating.nil? ? "" :review.rating.to_s)
@@ -92,15 +120,55 @@
             end
             values.append(cols)
         end
 
         page = 1
-        limit = 500
+        limit = 100
         values.each_slice(limit) do |value|
-            puts "[GoogleSheetProcessor] Insert rows(#{page}/#{(values.length/limit).ceil + 1}) to #{sheetID}-#{sheetName}"
+            puts "[GoogleSheetProcessor] Insert rows page:(#{page}/#{(values.length/limit).ceil + 1}) #{sheetInsertType} to #{spreadsheetID}"
             page += 1
-            googleAPI.request("https://sheets.googleapis.com/v4/spreadsheets/#{sheetID}/values/#{sheetName}!A1:append?valueInputOption=RAW", "POST", {:values => value})
+            if sheetInsertType == "insert"
+                googleAPI.request("https://sheets.googleapis.com/v4/spreadsheets/#{spreadsheetID}:batchUpdate", "POST", {
+                    "requests": [
+                        {
+                            "insertRange": {
+                                "range": {
+                                    "sheetId": sheetId,
+                                    "startRowIndex": sheetInsertAt,
+                                    "endRowIndex": sheetInsertAt + value.length
+                                },
+                                "shiftDimension": "ROWS"
+                            }
+                        },
+                        {
+                            "pasteData": {
+                                "data": rowsToString(value),
+                                "type": "PASTE_NORMAL",
+                                "delimiter": ",",
+                                "coordinate": {
+                                    "sheetId": sheetId,
+                                    "rowIndex": sheetInsertAt,
+                                }
+                            }
+                        }
+                    ]
+                })
+            elsif sheetInsertType == "append"
+                googleAPI.request("https://sheets.googleapis.com/v4/spreadsheets/#{spreadsheetID}/values/#{sheetName}!A1:append?valueInputOption=RAW", "POST", {:values => value})
+            end
         end
 
         return reviews
+    end
+
+    private
+    def rowsToString(rows)
+        string = ""
+        rows.each do |row|
+            if string != "" 
+                string += "\n"
+            end
+            string += "\"#{row.map{ |v| v.gsub('"','\"') }.join('","')}\""
+        end
+        return string
     end
 end
\ No newline at end of file