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

Recommended Posts

Frequently used processes in SpreadSheet
Frequently used methods in Active Record
Frequently used docker-compose command
Frequently used Java generics
Docker Frequently used commands
Summary of frequently used commands in Rails and Docker
IntelliJ Frequently Used Operation Notes
Gem often used in Rails
rails console Frequently used operations
Matcher often used in RSpec
Frequently used Maven command collection
Syntax examples often used in Java
About methods often used in devise
Test API often used in AssertJ
Summary of frequently used Docker commands
Expression used in the fizz_buzz problem
Commands often used in MySQL operations
[Docker] Other frequently used (probably) Docker Instructions
Ruby methods often used in Rails
Introduction to Docker (1) Frequently used commands