google_sheet_update.yml

name: Google Sheet Update
on:
  pull_request_target:
    types: [opened]

jobs:
  get-delivery-files:
    runs-on: ubuntu-latest
    outputs:
      filenames: ${{ steps.files.outputs.added }}
    
    steps:
      - name: Get filenames of any deliveries being added
        id: files
        uses: Ana06/get-changed-files@v2.3.0
        with:
          format: 'json'
          filter: |
            deliveries/*.md
            maintenance_deliveries/*.md

  check-no-success:
    needs: get-delivery-files
    runs-on: ubuntu-latest
    outputs:
      has-succeeded: ${{ steps.check-no-success.outputs.has-succeeded }}
    
    steps:
      - name: Check that the workflow didn't yet run successfully in this PR
        uses: actions/github-script@v5
        id: check-no-success
        if: needs.get-delivery-files.outputs.filenames != '[]'
        with:
          script: |
            // https://octokit.github.io/rest.js/v18
            const runs = await github.rest.actions.listWorkflowRuns({
              owner: context.repo.owner,
              repo: context.repo.repo,
              workflow_id: 'google_sheet_update.yml',
              status: 'success',
              per_page: 100
            });
            const pr_commits = await github.rest.pulls.listCommits({
              owner: context.repo.owner,
              repo: context.repo.repo,
              pull_number: ${{ github.event.number }},
              per_page: 100
            });
            const runs_shas = runs.data.workflow_runs.map(run => {return run.head_sha});
            const pr_shas = pr_commits.data.map(c => {return c.sha});
            core.setOutput("has-succeeded", pr_shas.filter(s => runs_shas.includes(s)).length != 0);

  update-sheet:
    needs: [ get-delivery-files, check-no-success ]
    if: needs.get-delivery-files.outputs.filenames != '[]' && needs.check-no-success.outputs.has-succeeded == 'false'
    runs-on: ubuntu-latest
    strategy:
      max-parallel: 1
      fail-fast: false
      matrix:
        filename: ${{ fromJson(needs.get-delivery-files.outputs.filenames) }}
    steps:
      - uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.head.sha }}

      - name: Parse delivery file 
        id: parse-delivery
        uses: w3f/parse-milestone-delivery-action@master
        with:
          path: "${{ github.workspace }}/${{ matrix.filename }}"
          strict: "false"

      - name: Get application file
        run: wget "https://raw.githubusercontent.com/w3f/Grants-Program/master/applications/${{ steps.parse-delivery.outputs.application_document }}"

      - name: Parse application file
        id: parse-grant
        uses: w3f/parse-grant-application-action@master
        with:
          path: "${{ steps.parse-delivery.outputs.application_document }}"
          strict: "false"
      
      - name: Get delivery date
        id: date
        run: |
          delivery_date=${{ github.event.pull_request.created_at }}        
          echo "::set-output name=date::$(date -d "$delivery_date" +%d/%m/%Y)"
  
      - name: Write data to sheet
        uses: jroehl/gsheet.action@v2.1.1
        with:
          spreadsheetId: ${{ secrets.SPREADSHEET_ID }}
          commands: |
            [
              {
                "command": "appendData",
                "args":
                {
                  "data": [[
                    "=IFERROR(HYPERLINK(\"#gid=0&range=\" & MATCH(INDIRECT(CONCATENATE(\"B\", TEXT(ROW(), \"#\"))), Legal!$D:$D, 0) & \":\" & MATCH(INDIRECT(CONCATENATE(\"B\", TEXT(ROW(), \"#\"))), Legal!$D:$D, 0), INDEX(Legal!$A$2:$A,MATCH(INDIRECT(CONCATENATE(\"B\", TEXT(ROW(), \"#\"))),Legal!$D$2:$D,0))), \"\")",
                    "${{ steps.parse-grant.outputs.project_name }}",
                    "${{ steps.parse-delivery.outputs.milestone_number }}",
                    "",
                    "Not Yet",
                    "=INDEX(Legal!$W$2:$W,MATCH(INDIRECT(CONCATENATE(\"B\", TEXT(ROW(), \"#\"))),Legal!$D$2:$D,0))",
                    "${{ github.event.pull_request.html_url }}",
                    "",
                    "${{ steps.date.outputs.date }}",
                    "",
                    "=IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2)),\"\")",
                    "=IF(INDIRECT(ADDRESS(ROW(),5))=\"Done\",VLOOKUP(INDIRECT(ADDRESS(ROW(),2)),Milestones!B:BE,SWITCH(INDIRECT(ADDRESS(ROW(),3)),1,19,2,23,3,27,4,31,5,35,6,39,7,43,8,47,9,51,10,55),FALSE),\"\")",
                    "=IF(INDIRECT(ADDRESS(ROW(),5))=\"Done\",VLOOKUP(INDIRECT(ADDRESS(ROW(),2)),Milestones!B:BE,SWITCH(INDIRECT(ADDRESS(ROW(),3)),1,20,2,24,3,28,4,32,5,36,6,40,7,44,8,48,9,52,10,56),FALSE),\"\")"
                  ]],
                  "worksheetTitle": "Evaluation",
                  "minCol": 1,
                  "valueInputOption": "USER_ENTERED"
                }
               }
            ]
        env:
          GSHEET_CLIENT_EMAIL: ${{ secrets.GSHEET_CLIENT_EMAIL }}
          GSHEET_PRIVATE_KEY: ${{ secrets.GSHEET_PRIVATE_KEY }}