Plugin e1_ws_googlesheets

Browse: Home / ERPIO One / Pluginy / Plugin e1_ws_googlesheets

Stiahnutie pluginu – https://erpio.link/plugingooglesheets

Vytvoriť projekt a service account

https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts

Stiahnuť json file s private key a toto umiestniť vedľa dll pluginu, json file treba premenovať na google_credentials.json

• V MW vytvoriť datasource s pluginom, model je Google Spreadsheets
• do SQLcommandu sa daju písať filtre napr. A <> ” (kde A je stlpec, podľa ktorého chcem filtrovať), dávam zatiaľ default 1=1

• sú tam predvolené parametre (cez tlačidlo s tromi bodkami)

• SpreadsheetId beriem z url adresy daneho dokumentu, súbor je potrebné dať zdielať s právami redaktor na e-mail servisného konta

• Range môže byť buď názov hárku alebo názov pomenovanej oblasti alebo rozsah buniek, napr. A1:E10

Nad setom sú pripravené akcie:

Podmienky (do poľa query filter/definition/command SQL) navigačných alebo detail akcií treba písať ako AND A <> ”, lebo defaultne sa tam použije filter z datasource a k nemu sa pridá tento z navigačnej akcie

Exporty do PDF, zobrazenie grafu

/*
//All requests must include id in the path and a format parameter
//https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export

      //FORMATS WITH NO ADDITIONAL OPTIONS
      //format=xlsx       //excel
      //format=ods        //Open Document Spreadsheet
      //format=zip        //html zipped          

      //CSV,TSV OPTIONS***********
      //format=csv        // comma seperated values
      //             tsv        // tab seperated values
      //gid=sheetId             // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID

      // PDF OPTIONS****************
      //format=pdf     
      //size=0,1,2..10             paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5  
      //fzr=true/false             repeat row headers
      //portrait=true/false        false =  landscape
      //fitw=true/false            fit window or actual size
      //gridlines=true/false
      //printtitle=true/false
      //pagenum=CENTER/UNDEFINED      CENTER = show page numbers / UNDEFINED = do not show
      //attachment = true/false      dunno? Leave this as true
      //gid=sheetId                 Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. 
                                   // Leave this off for all sheets. 
      // EXPORT RANGE OPTIONS FOR PDF
      //need all the below to export a range
      //gid=sheetId                must be included. The first sheet will be 0. others will have a uniqe ID
      //ir=false                   seems to be always false
      //ic=false                   same as ir
      //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
      //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
      //r2=End Row number
      //c2=End Column number

    https://docs.google.com/spreadsheets/d/1HKX80ejxUkD2WZUAZCAYPkUVneby-iH3kTjWHiXWGMU
    /export?exportFormat=pdf&format=pdf
    &size=A4
    &portrait=true
    &fitw=true       
    &top_margin=0.50
    &bottom_margin=0.50 
    &left_margin=0.50
    &right_margin=0.50           
    &sheetnames=false&printtitle=false
    &pagenum=false
    &gridlines=false
    &fzr=FALSE 
    &gid=0

    TABULKA
    https://docs.google.com/spreadsheets/d/1HKX80ejxUkD2WZUAZCAYPkUVneby-iH3kTjWHiXWGMU/export?exportFormat=pdf&format=pdf&size=A4&portrait=true&fitw=true&top_margin=0.50&bottom_margin=0.50&left_margin=0.50&right_margin=0.50&sheetnames=false&printtitle=false&pagenum=false&gridlines=false&fzr=FALSE&gid=0

    https://docs.google.com/spreadsheets/d/1HKX80ejxUkD2WZUAZCAYPkUVneby-iH3kTjWHiXWGMU/export?exportFormat=pdf&format=pdf&size=A4&portrait=true&fitw=true&top_margin=0.50&bottom_margin=0.50&left_margin=0.50&right_margin=0.50&sheetnames=false&printtitle=false&pagenum=false&gridlines=false&fzr=FALSE&gid=1715368235                

    GRAF
    https://docs.google.com/spreadsheets/d/1HKX80ejxUkD2WZUAZCAYPkUVneby-iH3kTjWHiXWGMU/export?exportFormat=pdf&format=pdf&size=A4&portrait=true&fitw=true&top_margin=0.50&bottom_margin=0.50&left_margin=0.50&right_margin=0.50&sheetnames=false&printtitle=false&pagenum=false&gridlines=false&fzr=FALSE&gid=10099204
    */