google_sheet_update.yml

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

jobs:
  get-delivery-once:
    runs-on: ubuntu-latest
    outputs:
      has-succeeded: ${{ steps.check-no-success.outputs.has-succeeded }}
      filenames: ${{ steps.delivery.outputs.filenames }}
      milestone-number: ${{ steps.delivery.outputs.milestone-number }}
      application-filename: ${{ steps.delivery.outputs.application-filename }}
    steps:
      - uses: actions/checkout@v2

      - name: Parse delivery file
        id: delivery
        uses: ./.github/workflows/composite/get_delivery

      - name: Check that the workflow didn't yet run successfully in this PR
        uses: actions/github-script@v5
        id: check-no-success
        if: steps.delivery.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: # TODO run for each delivery if PR contains more than one (see also get_delivery.yml)
    needs: get-delivery-once
    if: needs.get-delivery-once.outputs.filenames && needs.get-delivery-once.outputs.has-succeeded == 'false'
    runs-on: ubuntu-latest
    steps:
      - name: Get application file
        run: wget "https://raw.githubusercontent.com/w3f/Grants-Program/master/applications/${{ needs.get-delivery-once.outputs.application-filename }}"

      - name: Parse application file
        id: parse-grant
        uses: w3f/parse-grant-application-action@v1
        with:
          path: "${{ needs.get-delivery-once.outputs.application-filename }}"

      - name: Get current date
        id: date
        run: echo "::set-output name=date::$(date +'%d/%m/%Y')"

      - name: Write data to sheet
        uses: jroehl/gsheet.action@v1.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 }}",
                    "${{ needs.get-delivery-once.outputs.milestone_number }}",
                    "",
                    "Not Yet",
                    "=IFERROR(SWITCH(INDIRECT(ADDRESS(ROW(),COLUMN()-1)), \"Not Yet\", 3, \"In Progress\", 2, \"Asked for Changes\", 1, \"Final Check\", 2, \"Done\", 0, \"Terminated\", 0))",
                    "=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 }}"
                  ]],
                  "worksheetTitle": "Evaluation",
                  "minCol": 1,
                  "valueInputOption": "USER_ENTERED"
                }
               }
            ]
        env:
          GSHEET_CLIENT_EMAIL: ${{ secrets.GSHEET_CLIENT_EMAIL }}
          GSHEET_PRIVATE_KEY: ${{ secrets.GSHEET_PRIVATE_KEY }}