Automatically uploading Google Sheet as CSV using Apps Script

Google Apps Script can be used to automate Google products with javascript. I needed to create a csv from the sheet and then upload it to an outside service once per day. Start by cliking Extensions -> Apps Script:

function onOpen() {
  SpreadsheetApp.getActive().addMenu('My functions', [
    {
      name: 'Upload CSV',
      functionName: 'uploadCSV',
    },
  ])
}

function uploadCSV() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  const csv = convertRangeToCsv(sheet)
  const blob = Utilities.newBlob(csv, 'text/csv', 'file.csv')

  const form = {
    file: blob,
  }

  let uploadRes = UrlFetchApp.fetch('https://example.com/upload', {
    method: 'POST',
    payload: form,
    headers: {
      Authorization: 'Bearer xx',
    },
  })

  Logger.log(uploadRes)
}

function convertRangeToCsv(sheet) {
  // From: https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1
  const activeRange = sheet.getDataRange()
  try {
    const data = activeRange.getValues()
    const delimiter = ';'
    let csvOutput = undefined

    if (data.length > 1) {
      let csv = ''
      for (let row = 0; row < data.length; row++) {
        for (let col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(delimiter) != -1) {
            data[row][col] = '"' + data[row][col] + '"'
          }
        }

        if (row < data.length - 1) {
          csv += data[row].join(delimiter) + '\r\n'
        } else {
          csv += data[row]
        }
      }
      csvOutput = csv
    }
    return csvOutput
  } catch (err) {
    Logger.log(err)
    Browser.msgBox(err)
  }
}

Save the code and you will have a new menu item in the toolbar called My functions -> Upload CSV. You can run it manually from here or trigger the upload with a cron.

Triggering the function once per day

From the left menu select Triggers -> Add Trigger and choose the uploadCSV function -> Time-Driven and select how often you want the script to run.

• UrlFetch
• SpreadsheetApp
• Utilities.newBlob