CODEWITHSUNDEEP
restgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
pery mimon
pery mimon

Reputation: 8317

How find location of cell with some value

I need to find a row in my spreadsheet that contains some data and delete it. all I know is the id inside a cell on the row.

How can I perform a search for the location of the cell with google REST API

Like for example: 

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:find?value=123456

response:
 sheet1! B2:B2

Also: I also want to support cut&paste from one sheet to another. so put metadata on the row can't work because it lost when copying cells from the row to another row. ( there is no REST API to cut&paste dimension (row||column) from one sheet to another.

If there is a way to copy&paste dimension between sheet I simple can use


POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter

{
  "dataFilters": [
    {
      object (DataFilter)
    }
  ],
  "majorDimension": enum (Dimension),
  "valueRenderOption": enum (ValueRenderOption),
  "dateTimeRenderOption": enum (DateTimeRenderOption)
}

to find the row and delete it.

Upvotes: 1

Views: 2389

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to retrieve the row number by searching a value using Sheets API.
  • You want to delete the rows by searching a value using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, in the current stage, the row number cannot be directly retrieved by searching a value with Sheets API. And also, the rows cannot be deleted by searching a value using Sheets API. I'm not sure whether these are added at the future update. So in the current workaround, how about the following workarounds?

Workaround 1:

The flow of this workaround is as follows.

  1. Retrieve all sheet names and sheet ID using the method of "spreadsheets.get" in Sheets API.
  2. Retrieve all values from the sheet in the Spreadsheet using the method of "spreadsheets.values.batchGet" Sheets API.
  3. Search the sheet name and row number from the retrieved values using a value.
    • In this case, it is required to prepare a script.
  4. Delete rows using the method of "spreadsheets.batchUpdate" in Sheets API.

By this flow, your goal can be achieved.

Workaround 2:

In this workaround, Web Apps created by Google Apps Script is used as an API.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Prepare script.

Please copy and paste the following script to the script editor. This script is for the Web Apps. In this script, Sheets API is used. So please enable Sheets API at Advanced Google services.

function doGet(e) {
  var findText = e.parameter.value;
  var spreadsheetId = e.parameter.spreadsheetId;
  var deleteRows = e.parameter.deleteRows && e.parameter.deleteRows.toLowerCase() === "true";
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var ranges = ss.createTextFinder(findText).findAll();
  if (deleteRows === true) {
    var requests = ranges.reverse().map(r => ({deleteDimension:{range:{dimension:"ROWS",sheetId:r.getSheet().getSheetId(),startIndex:r.getRow() - 1,endIndex:r.getRow()}}}));
    Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
  }
  var res = ranges.map(r => ({range: `'${r.getSheet().getSheetName()}'!${r.getA1Notation()}`, sheetId: r.getSheet().getSheetId()}));
  return ContentService.createTextOutput(JSON.stringify(res));
}
  • In this case, the GET method is used. When you want to run the function by giving the large data, you can use doPost() instead of doGet().

3. Deploy Web Apps.

  1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
  2. Select "Me" for "Execute the app as:".
    • By this, the script is run as the owner.
    • Here, when "Anyone" is set, the script is run as each user. In this case, it is required to share the script to each user. And the access token is required to be used. Please be careful this.
  3. Select "Anyone, even anonymous" for "Who has access to the app:".
    • In this case, no access token is required to be request. I think that as the test case, I recommend this setting.
    • Of course, you can also use the access token. At that time, please set this to "Anyone".
  4. Click "Deploy" button as new "Project version".
  5. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  6. Click "OK".
  7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

3. Run the function using Web Apps.

This is a sample curl command for using Web Apps. Please set your Web Apps URL, spreadsheetId and the search value.

curl -GL \
  -d "spreadsheetId=###" \
  -d "value=sample" \
  "https://script.google.com/macros/s/###/exec"
  • When spreadsheetId=### and value=sample are used as the query parameter at doGet(e). For example, you can retrieve spreadsheetId using e.parameter.spreadsheetId.

  • At above script of Web Apps, when this curl command is run, the value like [{"range":"'Sheet1'!A1","sheetId":0},{"range":"'Sheet2'!B2","sheetId":###}] is returned. This is the result by searching a value of sample.

    • As a sample case, the value is searched from all sheets in the Spreadsheet. About this, you can modify the script for your actual situation.

  • If deleteRows=true is used like below, the value is searched from all sheets in the Spreadsheet and the searched rows are deleted.

    curl -GL \
  -d "spreadsheetId=###" \
  -d "value=sample" \
  -d "deleteRows=true" \
  "https://script.google.com/macros/s/###/exec"

Note:

  • This sample script is used with V8.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

Related Questions