0

I have a web app that calculates quotations for building houses using a Google Sheet. Once the client completes a form with the inputs, I calculate the cost and generate a spreadsheet with the corresponding values to send to the client via email.

How can I export only the corresponding sheet to PDF, and not all the other that are useful for us (but not for the client).

I am using Google's PHP API client, both the Sheets API and the Drive API, and there is no method to export just one sheet: drive.files.export method exports the whole spreadsheet.

If I try using the spreadsheets.sheets.copyTo method, I can copy the corresponding sheet to a new spreadsheet, but since the values of this sheet are calculated by formulas with dependencies on the rest of the sheet, I get cell value errors in the newly created spreadsheet.

I don't know if I can grant access to the new spreadsheet to another spreadsheet grammatically.

Anyone knows a workaround to export a single sheet?

ÁngelBlanco
  • 438
  • 4
  • 11
  • Possible duplicate of [Export Single Sheet to PDF in Apps Script](https://stackoverflow.com/questions/38335143/export-single-sheet-to-pdf-in-apps-script) – tehhowch Mar 18 '18 at 20:54
  • Also related: https://stackoverflow.com/questions/49197358/generate-pdf-of-only-one-sheet-of-my-spreadsheet – tehhowch Mar 18 '18 at 21:01
  • It is quite similar, but I am generating everything from my php server, so I use the php client library. Everytime a user sends the form, I make a copy of my spreadsheet template and fulfull the users inputs. There are 8 different sheets in the spreadseet, each one calculating values from the other sheets starting with the user inputs. After the data is fulfilled I have to export just 1 of the sheets to PDF to send it via email. All this from my apache server which is receiving the http form. – ÁngelBlanco Mar 18 '18 at 22:15

1 Answers1

0

Per Export Single Sheet to PDF in Apps Script, have you tried adjusting sheet visibility? In Sheets REST API, you can do this with an Update Sheet Properties request. You can bundle several of these into a batch request, to ensure all but the desired sheet is hidden prior to conversion.

In REST, this looks like:

{
  "requests": [
    "updateSheetProperties" {
      "properties": {
        "sheetId": ...
        "hidden": true,
      }
    "fields": "hidden"
    },
    "updateSheetProperties" {
      ...
  ],
  "includeSpreadsheetInResponse": false
}

In the PHP client, you'd use the batchUpdateSpreadsheetRequest method and this request.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • Thank you very much, I will try this first thing tomorrow morning, it looks promising!...:) – ÁngelBlanco Mar 18 '18 at 22:11
  • I am trying the following code: $body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(); $requests = array(); foreach ($sheetIds as $sId) { $request= new Google_Service_Sheets_UpdateSheetPropertiesRequest(); $request_prop = new Google_Service_Sheets_SheetProperties(); $request_prop->setSheetId($sId); $request_prop->setHidden(true); $request->setProperties($request_prop); $requests[] = $request; } $body->setRequests($requests); $response = $sheet_service->spreadsheets->batchUpdate($spId, $body); – ÁngelBlanco Mar 19 '18 at 18:22
  • And I keep getting this error: Fatal error: Uncaught exception 'Google_Service_Exception' with message '{ "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"properties\" at 'requests[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"properties\" at 'requests[1]': Cannot find field.\nInvalid JSON payload received... – ÁngelBlanco Mar 19 '18 at 18:25
  • By the way, what is the correct way to post this info? I dont feel I am doing it correctly....:) – ÁngelBlanco Mar 19 '18 at 18:26
  • @ÁngelBlanco It looks like you're doing it correctly, so I'm not sure why it's failing. (I don't actually use the PHP client, so I can't clarify the correct sequence.) Could possibly be worthy of its own question (and in that question the code format will actually be possible too!) – tehhowch Mar 19 '18 at 19:18
  • I already did: https://stackoverflow.com/questions/49373536/hide-sheets-with-google-php-spreadsheets-client – ÁngelBlanco Mar 19 '18 at 22:45