Frequently used processes in SpreadSheet

Introduction

We won't go through the steps to get access to your spreadsheet here. It summarizes the processes that are frequently used when creating various tools.

sheet.rb

class Sheet
    #Initialize by passing the id of the sheet
    # sheet = Sheet.new('xxxxxxxxxxxxxxxxxx')
    #Use like
    def initialize(sheet_key)
        @sheet_key = sheet_key
        @config_file = "path/to/google_drive_config.json"
        @session = GoogleDrive::Session.from_config(@config_file)
        begin
            @sheet = @session.spreadsheet_by_key(@sheet_key)
        rescue => e
            puts e.message
        end
        @ws = nil
    end

    #Set the sheet to use when reading and writing
    # sheet = Sheet.new('xxxxxxxxxxxxxxxxxx')
    # sheet.setWorksheet('Sheet 1')
    #Use like
    def setWorksheet(sheet_name)
        @ws = worksheet(sheet_name.to_s)
    end

    #Check if the sheet exists
    # exist_sheet = sheet.isExistWorksheet('Sheet 1')
    #Use like
    def isExistWorksheet(sheet_name)
        if self.worksheet(sheet_name).nil? then
            return false
        end
        return true
    end

    #Hash when using a sheet like a config or DB_key_data with arr as the key_Make all lists hash
    # hash_key_arr = [id, value, created_at]
    # data_list = [[1, 'hoge', '2020-01-01'], [2, 'hogehoge', '2020-01-02']]
    #To
    # [
    #    {id: 1, value: 'hoge', created_at: '2020-01-01'},
    #    {id: 2, value: 'hogehoge', created_at: '2020-01-02'}
    # ]
    #It returns like this.
    def createHash(hash_key_arr, data_list)
        ret = []
        data_list.each{|data|
            tmp = {}
            hash_key_arr.each_with_index{|hash,index|
                tmp[hash] = data[index]
            }
            ret.push(tmp)
        }
        return ret
    end

    # getRange(start_row, start_col, end_row - start_row, end_col - start_col).getValues()
    #Equivalent to.
    # start_row,start_If col is nil, 1 and end respectively_row,end_num if col is nil_rows,num_Get as cols.
    def getData(start_row=nil, end_row=nil, start_col=nil, end_col=nil)
        ret = []
        if start_col.nil? then
            start_col = 1
        end
        if end_col.nil? then
            end_col = @ws.num_cols
        end
        if start_row.nil? then
            start_row = 1
        end
        if end_row.nil? then
            end_row = @ws.num_rows
        end
        for row_index in start_row..end_row do
            data = []
            for col_index in start_col..end_col do
                data.push(@ws[row_index, col_index])
            end
            ret.push(data)
        end
        return ret
    end

    # start_row,start_write as cell to start col_Write with datas
    def writeData(start_row, start_col, write_datas)
        write_datas.each_with_index{|rows, row_index|
            rows.each_with_index{|col, col_index|
                @ws[start_row + row_index, start_col + col_index] = col
            }
        }
        @ws.save
    end

    #Get the number of last lines
    def getLastRow
        @ws.num_rows
    end

    #Get the number of last columns
    def getLastCol
        @ws.num_cols
    end

    #Get worksheet by title
    def worksheet(title)
        @sheet.worksheet_by_title(title)
    end

    #Created by specifying a name
    def add_worksheet(title, rows, cols)
        @sheet.add_worksheet(title, max_rows = rows, max_cols = cols)
    end

    #Make a copy
    def copy_worksheet(base, title)
        num_rows = 1000
        num_cols = 14
        new_sheet = add_worksheet(title, num_rows, num_cols)
        origin_data = worksheet(base)
        num_rows = origin_data.num_rows
        num_cols = origin_data.num_cols
        for row_index in 1..num_rows do
            for col_index in 1..num_cols do
                new_sheet[row_index, col_index] = origin_data[row_index, col_index]
            end
        end
        new_sheet.save
    end
end

reference

https://qiita.com/koshilife/items/4baf1804c585690fc295